Solved

Database Table Design Question

Posted on 2011-09-21
2
424 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 76

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

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…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now