SQL Table Design

[carleihar]

Hi, I'm new here, and I'm not positive this is posted in the right forum but I have a question regarding how I should set up my SQL tables.

 

I'm pretty new to php and mySQL so I apologize if this sounds noobish.

 

So I have a website with multiple users.

Each user can have multiple "items".

Each item can have multiple characteristics (i.e. Color, type, price, ext.)

Each "item" needs a specific ID, no two items are the same.

 

How should I design my databases? Is it possible to have a table dedicated to every user with its "items", and then have a table dedicated to each item?

 

Thanks so much!

 

 

[cags]

Have you ever heard of the term normalisation? If not I'd recommend reading up on it, but fair warning, it's a bit of a dry subject. At it's most basic, if you have two tables that have a many-to-many relationship you introduce a third table. In your case obvious candidates for table names are 'users', 'items' and 'characteristics' each of these tables would have a unique key/id, ie user_id, item_id and characteristics_id.

 

Since an item can have many characteristics and a characteristic can belong to many items (ie you could have more than one item colored yellow), this calls for a table. Generally speaking this would be named 'item_characteristics'. This would be a table that would have foriegn keys for item_id and characteristics_id. Providing that an item can only belong to one user you would simply use a foreign key owner_id in the items table.

 

This of course assumes that there is a variable number of characteristics. If all items would have a color, type and price, they should instead be fields in the items table.

 

Thats my 2p worth anyway.

[jcombs_31]

I think that was explained pretty well.  I always like to take out a piece of paper and start with a simple ER diagram to get things going.  Some people may not but it helps me visualize what I'm creating.  It also just happens to be one of the first things I learned in my first DB class.  There are a few different forms of normalization but the key concept is to reduce the amount of redundant data.