I'm helping someone to design a database and for a specific case I'm not sure.

I have 10 fields - this will never change - that will always be filled and used in every query that could be coming up. Always.

So I came up with 2 solutions, normalized and not normalized


I would have to query the first solution like this;

    select n.Id
         , Type1Selector1.Info as Type1Selector1Info
         , Type1Selector2.Info as Type1Selector2Info
         , Type1Selector3.Info as Type1Selector3Info
         , Type1Selector4.Info as Type1Selector4Info
         , Type1Selector5.Info as Type1Selector5Info
         , Type2Selector1.Info as Type2Selector1Info
         , Type2Selector2.Info as Type2Selector2Info
         , Type2Selector3.Info as Type2Selector3Info
         , Type2Selector4.Info as Type2Selector4Info
         , Type2Selector5.Info as Type2Selector5Info

    from dbo.OptionOneMainTable n
         inner join dbo.OptionOneSecondTable Type1Selector1 on
                    Type1Selector1.Id = n.Id
                and Type1Selector1.Type = 1
                and Type1Selector1.Selector = 1
         inner join dbo.OptionOneSecondTable Type1Selector2 on
                    Type1Selector2.Id = n.Id
                and Type1Selector2.Type = 1
                and Type1Selector2.Selector = 2
         inner join dbo.OptionOneSecondTable Type1Selector3 on
                    Type1Selector3.Id = n.Id
                and Type1Selector3.Type = 1
                and Type1Selector3.Selector = 3
         inner join dbo.OptionOneSecondTable Type1Selector4 on
                    Type1Selector4.Id = n.Id
                and Type1Selector4.Type = 1
                and Type1Selector4.Selector = 4
         inner join dbo.OptionOneSecondTable Type1Selector5 on
                    Type1Selector5.Id = n.Id
                and Type1Selector5.Type = 1
                and Type1Selector5.Selector = 5
         inner join dbo.OptionOneSecondTable Type2Selector1 on
                    Type2Selector1.Id = n.Id
                and Type2Selector1.Type = 2
                and Type2Selector1.Selector = 1
         inner join dbo.OptionOneSecondTable Type2Selector2 on
                    Type2Selector2.Id = n.Id
                and Type2Selector2.Type = 2
                and Type2Selector2.Selector = 2
         inner join dbo.OptionOneSecondTable Type2Selector3 on
                    Type2Selector3.Id = n.Id
                and Type2Selector3.Type = 2
                and Type2Selector3.Selector = 3
         inner join dbo.OptionOneSecondTable Type2Selector4 on
                    Type2Selector4.Id = n.Id
                and Type2Selector4.Type = 2
                and Type2Selector4.Selector = 4
         inner join dbo.OptionOneSecondTable Type2Selector5 on
                    Type2Selector5.Id = n.Id
                and Type2Selector5.Type = 2
                and Type2Selector5.Selector = 5

and obviously the second solution would be

    select Id
         , Type1Selector1Info
         , Type1Selector2Info
         , Type1Selector3Info
         , Type1Selector4Info
         , Type1Selector5Info
         , Type2Selector1Info
         , Type2Selector2Info
         , Type2Selector3Info
         , Type2Selector4Info
         , Type2Selector5Info

    from dbo.OptionTwoMainTable

I'm not alone doing this database and I would personally take the second solution.

Should I try to convince the other person to go for solution two? He wants to go for solution one.

  • When you say "will always be filled", do you mean lot of inserts. what is scope of data growth? – user31045 Nov 27 '13 at 20:56
  • @madhu there will always be a kind of 1 to 1 mapping from Id to the 10 fields. you always have these 10 fields with values. you could have over 100,000 Id (if not millions) – Fredou Nov 27 '13 at 20:59
  • @madhu these 10 fields will be heavily read from the user interface, update could happen but not often – Fredou Nov 27 '13 at 21:01
  • Question: will you want to do calculations across the 10 columns? For example, average of the 10 values or sum/min/max of the 5 values per selector, etc.? If you will have such queries, just write - as an exercise - the query that finds for every ID, the selector (1 to 5) and type (1 to 2) with the highest value, using both designs. – ypercubeᵀᴹ Feb 27 '14 at 9:53

If the fields are always going to have a value and are always read as a group, then option 1 is normalized.

The column names could use some work, but I assume they have been obfuscated.

There is a third option which is to split the two types out into separate tables. This would be appropriate if either type grouping was optional and appear separately.

Option 2 is appropriate where the fields are independent of the id (Primary key). From your description, this is not the case.

Even if you do model the two types as separate tables in the design model, one of the implementations of subtypes is to incorporate them in the main table using nullable columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.