Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

db

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.

share|improve this question
    
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 at 9:53
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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