Basic question on DB design | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Basic question on DB design

Have a basic question… Here is an example: You have 3 "possible" options (order important) to store relating to one form. Is one of these two options better then the other? 1) a table with 3 columns: id of form, option, and order of option(1,2,or3). or 2) 3 tables (1, 2, and 3 denoting possible options) with 2 columns: id of form, and option
I will go for option1 for easy of data portability; Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Me too, also for easy manteinance.
Luis Martin
Moderator All postings are provided “AS IS” with no warranties for accuracy.
Yup, agreed. If (when?!) you ever add a 4th order your second design would require a new table.
thanks guys! rich
Agree with everyone here on this rich. You might consider reading up on normalization. From an "ease of programming" perspective, many times a programmer will choose #2. They end up later with a portability and sizing nightmare later though caused by denormalization. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Not only all that, but if you were to have a ‘dynamic’ stored procedure that returns the value of one of the options, with version 1 above you could do: create procedure getOptionDetails @optionNumber
select x from y where optionnumber = @optionNumber whereas with the second version you would have to use dynamic SQL to perform the same kind of query as you would have to select from a different table. e.g. Create procedure getOptionDetails @optionNumber
declare @sqlstring varchar(100)
set @sqlstring = ‘select x from optiontable’ + @optionNumber exec(@sqlstring) Not very nice and not very fast. Dave Hilditch. Ask a SQL Server Question