How to maintain integrity?

Question

Hello,

I am just curious how do you all create tables to maintain integrity?

tblUserProfile

UserId
EyeColorId
HairColorId
RelationShipStatusId

etc.

Each of these values like EyeColorId has a set of values like Brown,Black,Yellow,Green. Similary HairColorId has black, blonde, crimson etc and RelationShipStatusId has Divorced, Married, Single etc. Should i create different tables for each one of these? like

tblEyeColor

EyeColorId
EyeColorCode

then :-

tblHairColor

HairColorId
HairColorCode

and likewise keep creating tables? There are many such tables(approximately 20-25). If i keep creating these tables and make joins on them, it will terribly slow down my performance. How do you all maintain this sort of enum values? Should i keep a check constraint or should i keep making tables?

Answer 1

There is no need of creating tables if the number of options are fixed You can use Enum Type instead in your table. e.g. Column EyeColor will be Enum of Black, Brown, Blue

However I've never seen someone with Green Eyes. LOL

Answer 2

I would say that Color looks like it could be a single table that both hair and eye could both use. How important is it to your integrity to enforce the fact that no one should have blonde eyes or blue hair?

Some people go with the idea of a single lookup table, which would have an id, a group, and a value and perhaps a description for each row. The id and group would be the primary key, so you'd have id = 1 and group = 1 for hair as the BLONDE hair color, id = 1 and group = 2 for eyes as the HAZEL eye color, etc.

Others would deride this as a poor denormalized design.

The joins will only slow you down when the number for a particular query gets large.

My advice would be to do a normalized design until you have some data to suggest that it's performing poorly. When that happens, profile your app to find out where the problem is and refactor or denormalize appropriately.

I would say that indexing will have a greater impact on your performance than these JOINs. You might be guilty of premature optimization without data to support it.

Answer 3

That is the traditional method, yes.

Most modern RDBMSs will cache small tables like these lookups for extended periods of time, which alleviates the potential multi-join issues.

Also, you could pre-load all of the lookup tables in your application, and reverse-convert the enums to ids in code before accessing the database. This way you won't need joins in your queries.