Database design

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Database design

Post by Illusionist »

I'm working on a project withs ome friends, and i have one idea, but i dont think it will work or be the best. And i can't think of any other way, so i came to ask for opinions!

Ok i've got a table of items, all withtheir own id and all. Ok, now i have another table for the users items. The way i have that set up is something like:

UserID | i1 | i2 | ..... i29 | i30

the i1 - i30 are for the items in their inventory, and i was goignt o store the itemID in those places. Only now that i think about it this doesn't seem like the best of ideas. So i was wondering if anyone had any suggestions of what i should do...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

3 tables:
  1. Users (id being 1 field)
  2. Items (id being 1 field)
  3. Relation or whatever (being userid and itemid attachment)
this allows for a many-to-many relationship, which would cover the 1 item shared by many users, 1 user having many items scenarios...
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

ok, i have a users table, and an items table. But what would the design of the relation table lok like. Thats what i'm asking.
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

ok, i get it now. my user_ietms table would be like userID | itemID.

ok, so then would my select be something like...

Code: Select all

SELECT *,* FROM items,user_items WHERE items.ID=user_items.itemID AND user_items.userID={$_SESSIONї'id']}
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

probably just need:

SELECT i.* FROM items i, user_items r WHERE i.ID = r.itemID AND r.userID = {$_SESSION['id']}
Post Reply