Solved

Database Table Design Question

Posted on 2011-09-21
2
430 Views
Last Modified: 2012-05-12
I have a GUI that is launched within projects (there are many thousands of projects). The user has a selection that gives two choices. They choose one or the other (radio buttons that only allow 1 selection). I need to store this in a table. I'm wondering what the most efficient way to do this would be? The table will get very large over time.

Table Option 1
GUID (Pk) - VarChar2
Choice - VarChar2
*** When the user selects the option it will input the value chosen into the Choice field. The value inserted will be a text characters that are lets say 2-5 characters long. There will only be two options.

Table Option 2
GUID (Pk) - VarChar2
Choice1 - Number
Choice2 - Number
*** When the user selects an option it will set the selected value to 1 and the not selected value to 0. So the table will store both active and inactive values.

I also thought about doing the single choice option like Table Option 1, have the value be a number (where 1 is choice1 and 2 is choice2). For efficiency reasons I think that would be best, but anyone looking at the table will have no idea what number matches which what choice. Which is why I'm thinking of going with the Option 1.

What would be more efficient Table1 or Table 2? The reason I ask is because Table 1 has text to store, while Table 2 has two columns, but they are numerical values? Also, if you have a better table structure to store them in let me know. Just trying to get better at DB table design.
0
Comment
Question by:KGNickl
2 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 36575286
Will the 'options' every change over time and if so, will you need to preserve the previous selections?

For example:
today's options are:  1: Blue    2:Green

Next month:
1: Purple  2: Yellow

Will you need to know who selected Green last month?


I would also probably not go with a GUID as the PK.  GUIDs will take up a lot of space just to look up a 0 or 1.

With what you described for now, I would lean towards either a number(1) or char(1) column.
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 250 total points
ID: 36575328
I would lean toward the number(1) solution.  If you are worried about people not being able to understand it, create a look up table and a foreign key.  That way you can join to the table and get the description.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Read about achieving the basic levels of HRIS security in the workplace.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question