Solved

Database Table Design Question

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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 Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

929 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

15 Experts available now in Live!

Get 1:1 Help Now