Setting up tables for this problem... (Recommendation?)

[baden]

I'm creating an application that will track user's interests.

Apart from the regular user-related info, we will track Music and Restaurant interests.

I was thinking of creating a separate table for each, ie:
Music will contain: hip-hop, jazz, country, rock, r&b, etc...
Restaurant will contain: French, Italian, Vietnamese, Chinese, Japanese, Fusion, etc...

Can I link these all into a dynamic view of some sort, that will be updated whenever I add new fields to each table? (the view will contain a link to all tables and link the userID with their preferences)

- OR -

Would it be best to create one large table with all categories, where the attribute (field) prefixes the category, and (A) store these all in the User table, (B) store these in a separate table with just a FK to the User.PK ?

 

[bboffin]

Your approach will work but is a bit limited in that you will need a new table for each Interest and a separate column for each interest in the user table

It's better to have a table of Interests (2 columns : InterestID and description) containing two entries : Music and Restaurant in your example. This allows you to add more interests without changing the schema.

The define a separate table of InterestCategory (3 Columns : InterestCategoryID, InterestID, Description).

This will contain the entries for the type of music or restaurant along with the ID of the Interest so all the descriptions for all interests can be stored in one table.

Finally you're going to need a table, say UserCategoryLink, to link the user with the appropriate InterestCategories. This would have two columns UserID and InterestCategoryID. The two columns together form the primary key, the UserID is a foreign key into the User table and the InterestCategoryID is a foreign key into the InterestCategory table.

Thus we can now define a view along the lines of:

SELECT User.*, Interest.Description AS IDescription, InterestCategory.Description AS CDescription
FROM User 
INNER JOIN UserCategoryLink on User.UerID = UserCategoryLink.UserID
INNER JOIN InterestCategory ON UserCategoryLink.InterestCategoryID = InterestCategory.InterestCategoryID
INNER JOIN Interest ON Interest.InterestID = InterestCategory.InterestID

This will return a row for each InterestCategory defined for the user that includes the description of the Interest and the description of the Category.

This design of schema will work no matter how many Interests or how many categories there are in each interest.


Bob Boffin