Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 3rd 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?

share|improve this question

2 Answers 2

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.

share|improve this answer
    
Option 3 will give your application the best localization potential. –  Clever Idea Widgetry Jan 17 at 22:40

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))
share|improve this answer
    
Thanks for the answer. What are these option1 and 2 tables? –  codecool May 22 '13 at 13:45
    
I just put them there because you mentioned that there would be 6 dropdown values. I figured there would a key-value table for each dropdown option. –  druzin May 22 '13 at 13:48
1  
To add to Druzin's comment "…instead of in the database, where they really belong.". If those items are all in the operational database then they can be easily loaded into the data warehouse when you want to start analyzing the data. –  Greenstone Walker Sep 20 '13 at 2:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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