1

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?

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

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

CREATE TABLE Users
(
    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
ADD CONSTRAINT  FK_UserPreference_User FOREIGN KEY (UserID) REFERENCES Users(UserID);

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);

Visually:

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:

CREATE TABLE DateType
(
    DateTypeID int identity(1, 1) primary key clustered
    ,DateTypeName varchar(200)
);

CREATE TABLE UserDateType
(
    UserDateTypeID int identity(1, 1) primary key clustered
    ,UserID int
    ,DateTypeID int
    ,DateValue datetime
);

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

ALTER TABLE UserDateType
ADD CONSTRAINT  FKDateType FOREIGN KEY (DateTypeID) REFERENCES DateType(DateTypeID);

ALTER TABLE UserDateType
ADD CONSTRAINT  FKUserDate_UserID FOREIGN KEY (UserID) REFERENCES Users(UserID);


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.

6
  • Will do, all good points and thank you for commenting. – mskinner Aug 28 '14 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. – user3308043 Aug 29 '14 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. – user3308043 Aug 29 '14 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? – user3308043 Aug 29 '14 at 23:57
  • @mskinner Answer / edit that in, you got the check. – user3308043 Aug 30 '14 at 0:53
1

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

AddressTable
   AddressId
   Address1
   Address2
   CityId
   StateId
   ZipId

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:

CREATE TABLE #Users (
    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'))
    )
14
  • 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. – Kenneth Fisher Aug 29 '14 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. – Kenneth Fisher Aug 29 '14 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. – Kenneth Fisher Sep 1 '14 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. – Kenneth Fisher Sep 2 '14 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. – Kenneth Fisher Sep 3 '14 at 11:49

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.