I'm really battling an issue where I have a Users table that has a growing number of user characteristics (regligion, smoking preferences, etc). The strategy I've used thus far has been to add a column for each preference that keys off onto another table.

For example, if User XYZ has a RelgionId of 3, that could mean they're Christian. At runtime, if I need their religion, I join onto another table.

This strategy has worked so far. However, I'm getting concerned about the number of columns in the tables as the number of preferences is increasing. Also, this strategy leads to many joins if I need to get all values for a single user.

I'd like to find out the most normalized way of representing this data. Anybody have any ideas?

  • What does this have to do with "normalization"? What do you mean when you use that word?
    – philipxy
    May 13, 2020 at 22:47

2 Answers 2


You can do this sort of thing by creating what are essentially property bags of these values. This is not an approach that I am a huge fan of, but as with everything in SQL Server it has its place and your scenario might well be that case.

This is essentially the EAV approach, and Aaron Bertrand has a great writeup on this here to describe the pros and cons in more detail

    UserID int identity(1, 1) primary key clustered
    ,UserName varchar(200)

CREATE TABLE PreferenceType
    PreferenceTypeID int identity(1, 1) primary key clustered
    ,PreferenceName varchar(200)

CREATE TABLE PreferenceValue
    PreferenceValueID int identity(1, 1) primary key clustered
    ,PreferenceValueName varchar(200)
    ,PreferenceTypeID int

CREATE TABLE UserPreference
    UserPreferenceID int identity(1, 1) primary key clustered
    ,UserID int
    ,PreferenceValueID int

ALTER TABLE PreferenceValue
ADD CONSTRAINT  FKValue_Type FOREIGN KEY (PreferenceTypeID) REFERENCES PreferenceType(PreferenceTypeID);

ALTER TABLE UserPreference

ALTER TABLE UserPreference
ADD CONSTRAINT  FK_UserPreference_Value FOREIGN KEY (PreferenceValueID) REFERENCES PreferenceValue(PreferenceValueID);

insert into Users (UserName)
Values ('User1' ) --tblUserID = 1
, ('User2' );   --tblUserID = 2

Insert into PreferenceType  (PreferenceName)
Values ('Religion') --Type1
, ('Smoker'); --Type 2

Insert into PreferenceValue (PreferenceValueName, PreferenceTypeID)
Values ('Christian', 1) --Value 1
, ('Muslim', 1) --Value 2
, ('Non Smoker', 2)  --Value 3
, ('Smoker' , 2); -- Value 4

--User 1 is a Christian Smoker
--User 2 is a Muslim Non-Smoker
Insert into UserPreference (UserID, PreferenceValueID)
Values (1, 1)
, (1, 4)
, (2, 2)
, (2, 3);


enter image description here

So that when you want to get the information for User 1 you would run this select:

select u.UserID, u.UserName, pv.PreferenceValueName, pt.PreferenceName
From Users u
join UserPreference up on u.UserID = up.UserID
join PreferenceValue pv on up.PreferenceValueID = pv.PreferenceValueID
join PreferenceType pt on pv.PreferenceTypeID = pt.PreferenceTypeID
Where u.UserName = 'User1';

You would get the following results in the format of

UserID, UserName, PreferenceValue, PreferenceType

1, User1, Christian, Religion

1, User1, Smoker, Smoker

To include Date values within a schema similar to this, playing off of the tables that were already created but adding two new ones, you could do something like the following:

    DateTypeID int identity(1, 1) primary key clustered
    ,DateTypeName varchar(200)

    UserDateTypeID int identity(1, 1) primary key clustered
    ,UserID int
    ,DateTypeID int
    ,DateValue datetime

    uq_UserDateType on UserDateType (UserID, DateTypeID);  --Optional. To only allow one row per type.  



Insert into DateType (DateTypeName)
Values ('BirthDate') --Type1
, ('Anniversary'); --Type 2

Insert into UserDateType (UserID, DateTypeID, DateValue)
Values (1, 1, '01/01/1980') --User 1 has a Birthday of 01/01/1980
, (1, 2, '01/01/2000')  --User 1 has an Anniversary of 01/01/2000
, (2, 1, '02/01/1995');  --User 2 has an Birthday of 01/01/2000

By having these kinds of tables you can dynamically add the new date types and relate them to a user in this way.

As mentioned above, I am not sure that this is the ideal approach, as it certainly has its flaws, but this is a way that you can do this sort of thing with Date values.

  • Will do, all good points and thank you for commenting.
    – mskinner
    Aug 28, 2014 at 15:50
  • Yes... I definitely considered this approach. I'm going to run this through SQL and analyze a bit, I'll get back to you on this. Aug 29, 2014 at 7:34
  • The more I look at this whole problem, the more I like your approach. It's kind of gross though in terms of noramlization and SQL principles and is without a doubt an anti-pattern. But it seems more appealing than a lookup table per property. Aug 29, 2014 at 22:23
  • Where/how would I store different types of data? In this schema, every value is categorically nvarchar. What about storing int, datetime? I'm certain that an additional 'datatype', or something like that, column can be added, but where? Aug 29, 2014 at 23:57
  • @mskinner Answer / edit that in, you got the check. Aug 30, 2014 at 0:53

Normalization is a great concept and essential for well performing relational database. However it can become counter productive. For example you almost never see addresses broken up like this:

StateRegionTable    CityTable        ZipTable
   StateId             CityId           ZipId
   StateDesc           CityDesc         ZipDesc


It's just not all that useful and slows things down with extra joins. In your case I would recommend removing extra tables where the table only has an Id/Description pair.

So instead of having a ReligionTable and a SmokingTable and a GenderTable etc just have description columns with CHECK constraints. That does mean that if you want to add an extra value to the list you will have to modify the check constraint but with these types of tables it shouldn't happen all of that often. Where you do NOT want to do this is when your lookup table requires extra information. For example if your SmokingTable had an "Additional Insurance Cost" column or something like that then you want a separate lookup table.

Your table would look something like this:

    UserId INT,
    Religion VARCHAR(50) CONSTRAINT ck_Users_Religion CHECK (Religion IN ('Christian','Jew','Budist')),
    SmokingPreference CHAR(3) CONSTRAINT ck_Users_Smoking CHECK (SmokingPreference IN ('YES','NO'))
  • 1
    Not at all. I'm saying that you can over normalize. Again you frequently don't see state & zip tables because while you could normalize them it just isn't worth it. I'm only talking about cases where the lookup table would only be an ID/Description pair. Normalization is important. It can just be taken to far. Aug 29, 2014 at 12:12
  • 1
    No problem :) Although actually I'm talking about de-normalizing your data a little bit (not a lot). An EAV is something else. If you look at a datawarehouse structure sometime (definitely relational) you will see that they deliberately demoralize in certain places to gain reporting speed. Aug 29, 2014 at 22:55
  • 1
    Unfortunately I don't know much about C#. I would bet yes though. I would be very surprised if C# didn't have a unicode string data type. Sep 1, 2014 at 2:11
  • 1
    It's one of the major down sides to doing that type of schema. You have no good ways to deal with numeric and date values. I've done EAV type formats before but usually I've just bitten the bullet and had 3 or 4 different columns in the table, one of each data type. Over the years I've learned it didn't work terribly well and stopped using EAV type formats in general. Sep 2, 2014 at 2:24
  • 1
    I still feel that denormalizing to a small extent is your best bet. Look at a datawarehouse and you will see they denormalize (and for good reason) and in fact for what you are doing most databases I've seen do as well. Sep 3, 2014 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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