Anyone have an idea on how to solve this issue.
Im modeling my membership and user profile DB and run into one issue I have to solve.
I have this layout of tables.
- Membership (where all basic info is stored, like password etc.)
- UserProfile (where all extra info is stored, like name etc.)
In my application I will have two user groups (Parent/Kid), and both of them have the need of registering different types of profile data. Some are the same, but some is different.
How should I solve this? Should I just add all fields to the UserProfile table, and settle with the fact that some fields will be redundant.
Or should I create a new table for each user group and link them to the UserProfile?
Best Answer
First, you should never ever store user passwords! Read the following two articles before continuing: http://blog.moertel.com/posts/2006-12-15-never-store-passwords-in-a-database.html http://www.codinghorror.com/blog/2007/09/youre-probably-storing-passwords-incorrectly.html
Your user profile table design depends on extensibility and how common the user profile types are. In object oriented design you ould probably create a base class and derive one each for parents and kids. That's not possible in SQL though.
In general the best approach is to create a single table with columns for all profile types in this case. NULL values in columns are a common feature and cost next to nothing in terms of used table space. You can get away with multiple tables, but at the least it will make your queries very awkward, because you would need three tables: One for the common columns, and one for each profile. If you want to do a generic SELECT on the membership with added profile data, you have to join all three tables on it while with a single table you only have to join one.