1

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?

  • If you're using Sql Server try and use the inbuild SqlMembershipProvider profile features. That will be much simpler and time-saving than building your own tables – sa555 Aug 30 '13 at 9:50
  • Yes, im currently using the builtin Membership and profiles function in ASP.NET. But the issue is with the extra data I need to store. Like for parents I need to store if the parent is mom/dad, and that is not needed for kids. And for kids I want to store other info like birthday, which I dont need for parents. My question is, should I just gather all info in the UserProfile table and settle with not all fields beeing filled out. Or are there other ways? – Martin Aug 30 '13 at 11:00
1

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.

  • Thanks for your reply. Passwords are salted and hashed by default in ASP.NET Membership, so I think it is good. So then I will go for just one table in DB called UserProfile. And put all fields in there, and rather create classes in C# that manage the different user types. Thanks! – Martin Aug 30 '13 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.