Page 1 of 1

Database design

Posted: Sat Jun 05, 2004 11:13 am
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...

Posted: Sat Jun 05, 2004 11:23 am
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...

Posted: Sat Jun 05, 2004 11:24 am
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.

Posted: Sat Jun 05, 2004 11:39 am
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']}

Posted: Sat Jun 05, 2004 1:45 pm
by feyd
probably just need:

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