Users Characteristics

Question

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?

Answer 1

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:

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.

Answer 2

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