Usage of foreign key when the referenced table data will not change

Question

I have to design a table where user has 10 fields and 6 are dropdown. Some are single selections and some multiple. Dropdown data is supposed to change very rarely.

For e.g., the user is presented with days of the week and can select any number of days. It is going to be a one to many table UserDayPref(user_id, day). Now I have 3 choices:

1. Create table Days and make day field in UserDayPref a foreign key to Days table .
2. Make day field as string and save the name of the day.
3. Map Name of the day to an integer and save the integer.

I am inclined to implement the 3^rd option for this and all other dropdown fields. I am pretty sure data will edited rarely.

Is this the right way to do it? Or going with Foreign Keys is the right way?

Answer 1

Even If you want to go with the 3rd Option you can have a Foreign Key.

As in your example you are discussing about the days of the week they will never change.

In a case other then Days of the week where there is a probability of change, in my opinion you must go for a foreign-Key in order to maintain your Database Integrity.

Answer 2

Option 1 is your best way to go, just make sure that the Days table has a simple type id column which you will then reference in the UserDayPref table. Keeping the ID field a simple value will save on space and make for fast joins if the UserDayPref table grows large.

If you opt for option 3, you'd really be moving a part of the data into application code, since you'd be storing a key-value pair of the otherwise Days table ( and the other dropdown values ) in code instead of in the database, where they really belong. That's what a relational database is for ;-)

So, basically opt for something like this (using oracle syntax)

    CREATE TABLE USERS
    (
     id int primary key, 
     name varchar2(10)
    )

/

CREATE TABLE days
    (
     id char(1) primary key, 
     name varchar2(10)
    )

/

CREATE TABLE option1
    (
     id char(1) primary key, 
     name varchar2(10)
    )

/

CREATE TABLE option2
    (
     id char(1) primary key, 
     name varchar2(10)
    )

/


CREATE TABLE UserDayPref(user_id INT references users(id), 
                         day_id char(1) references days (id), 
                         option1_id char(1) references option1 (id),
                         option2_id char(1) references option2 (id))