Solved

Database Table Design Question

Posted on 2011-09-21
2
436 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 35

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

688 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