1

My issue is related to this question: Entity Framework One-To-One Mapping Issues

I have a Users table that already has a bunch of records. 

Users (Id, UserName, Password, FullName, Gender)

I need to add a bunch of notification options for each user:

NotifyForNewComment
NotifyForNewPost
NotifyWhenFriendSignsUp

I may have to add more options later, but there will always be a 1-1 relationship, so my question is whether to store these in a separate table, say UserSettings, or just add them as columns to the Users table.

In the linked question (above), the advice was to create a new table and make the UserId in the UserSettings table as the primary key (because otherwise, Entity Framework doesn't like it). If that's what I have to do, then I have a few questions regarding it:

  1. All my tables have an Id column. The UserSettings will not have an Id column then, since the UserId will be the primary key?

  2. I'd have to turn on Identity Insert for the UserSettings table, so that when I insert a new User record, I can also insert a UserSettings record with the UserId?

  3. Given that I already have a bunch of records in the Users table, what do I have to do if I'm going to introduce the new UserSettings table now which will have a 1-1 relationship with the Users table? Do I just run a script to add records for each user in the Users table with some default values? Or do I make it into a 0-1 relationship?

  4. Since it's a 1-1 relationship, should I not worry about a new table, and instead just add them as columns to the existing Users table?

Community
  • 1
  • 1
Prabhu
  • 12,995
  • 33
  • 127
  • 210
  • Can you add all the Notify columns to the user table as booleans and just toggle the active ones to true? – Steve Wellens Dec 04 '12 at 18:09
  • That would be the easiest for me, but I was just wondering if that's good database design... – Prabhu Dec 04 '12 at 18:17
  • 1
    Yes it is good database design. Easy and clear are good. Convoluted and hard are bad. It is very rare to have a need for a 1-to-1 table relationship. – Steve Wellens Dec 05 '12 at 03:28

1 Answers1

1

I think you are missing the point of a UserSettings table. It would have columns like:

  • UserSettingsId
  • UserId
  • Notification

It might also contain things like when the notification was created, whether it is currently enabled, and other information.

If you know exactly what the notifications are, and they are not going to change, then you might consider adding them as separate columns in the user table.

On the other hand, this is a natural 1-N relationship, and you should probably implement it as such.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786