using 2 tables in db

[thindery]

well i have created one table that contains my users info. password, email, info, etc. my other table i want to create will contain the user\'s favorite something. they can enter up to 5 items or so. i was told that i should make this favorites a new table that way if i wanted to search for relationships like the number one listed item, it would be easier. so is this a good way to organize the site? and how would i get these 2 tables to correspond since each user row will need a row in this 2nd table.

[gizmola]

The important question to ask, is what function your \"5 favorite things\" list will have on the site.

 

1. Is it possible there will be more than 5 favorite things in a list?

2. Will you be searching for all users that share the same favorite thing?

3. Is there a lot of additional information related to a \"favorite thing\" like perhaps a long description of why it\'s their favorite, or a related image or url.

 

 

If the answer to question 1, 2 or 3 is yes, then having a 2nd table is a good idea, since it will give you the flexibility and the functionality you need.

 

One additional question you would need to add is whether or not you need to maintain ordering of the list for a person (#1 Favorite, #2 Favorite etc). I\'m going to assume that this is how your system will operate (although at the cost of added complexity in your code).

 

Since favorites can\'t exist without the person for whom the favorite was created, the typical way to handle that is with a dependent relationship.

 

How this is structured, is that you would have a concatenated primary key (that is it will have 2 columns). The first column will be the same name and type as the primary key for your user table. The second column will act both as a way of uniquely identifying a row (needed by the PK) from any other row, and will also maintain the order of the user\'s items.

 

Here\'s how the tables would look:

 

 

I would need to know more about what the are \"favorites\" of. If the favorites list is standardized and people are choosing from a global list of favorites, then the structure could be further broken out to be 3 tables, but that would depend on having answers to my previous questions.