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
thanks,
rich

I will go for option1 for easy of data portability; Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Me too, also for easy manteinance.
Luis Martin
Moderator
SQL-Server-Performance.com 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
as
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
as
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
www.matiogi.com

]]>