locked
Normalization RRS feed

  • Question

  • Hello to all,

    I do not know if this is the section most appropriate to ask this question but since I have to develop a DB in SQLServer for an exam then I ask here hoping that someone will help.

    The problem is that in my DB tables that I'm developing, there is one that contains the coefficients used to calculate the formulas and these coefficients are almost always used for row-level operations. I was wondering if it is more correct to create the table in this way:

    TAB_COEF(YEAR, COEF_1, COEF_2, ...., COEF_N)

    withYEAR PrimaryKey

    or:

    TAB_COEF(YEAR, COEF_TYPE, COEF_VALUE)

    with YEAR and COEF_TYPE Primary key.

    What do you recommend?

    Wednesday, November 20, 2013 6:44 PM

Answers

  • Hello Archan87,

    Use the second Table structure.  Tomorrow you may need 50+ Coefficent value or 5+ coefficients for each contracts. IF you select the 2nd option you don't need to change the structure of the table latter.


    Regards, RSingh


    • Edited by Ch. Rajen SinghEditor Friday, November 22, 2013 6:18 AM
    • Proposed as answer by Sofiya Li Friday, November 22, 2013 9:18 AM
    • Marked as answer by Sofiya Li Thursday, November 28, 2013 6:37 AM
    Friday, November 22, 2013 6:16 AM
    Answerer

All replies

  • It depends.

    How do you access them, all at once, or one at a time?

    Is COEF_N fixed for every year, or variable?

    In most cases, I would suggest option 2.

    Wednesday, November 20, 2013 7:22 PM
    Answerer
  • The first one does not respect the first normal form.

    "

    • Eliminate duplicative columns from the same table.
    • Create separate tables for each group of related data and identify each row with a unique column (the primary key).

    "

    if you have 100 coefficient columns but use only 10 or so then 90 % of the space allocated is lost and contribute in slowing your queries.

    It could still be a good design if your number of columns is always fixed and  nearly 100% filled.  However, it is definitively not normalized.

    Take a look at this, it explains pretty well.  The quoted text comes from the first of the two articles.

    http://databases.about.com/od/specificproducts/a/firstnormalform.htm

    http://databases.about.com/od/specificproducts/a/normalization.htm


    • Edited by Antoine F Wednesday, November 20, 2013 9:19 PM
    Wednesday, November 20, 2013 9:15 PM
  • Sorry, I'll explain it better.

    I have 2 table

    1) Customer(code, amount, code_contract) where code is PK

    2) Contract(code_contract, coef_1, coef_2, ... coef_n) where code_contract id PK and FK on Customer(code_contract)

    Coefficent's total number is 50.

    Each contract may have from 1 to 5 coefficients.

    a typical query may be:

    SELECT amount * coef_1 + amount + (amount * (coef_34 - coef_23))

    FROM Contract inner join Customer on Contract .code_contract = Customer.code_contract

    where code > @start_code and code <= @end_code;

    Now that I have explained this: What do you recommend?

    Thursday, November 21, 2013 2:18 PM
  • Sorry, I'll explain it better.

    I have 2 table

    1) Customer(code, amount, code_contract) where code is PK

    2) Contract(code_contract, coef_1, coef_2, ... coef_n) where code_contract id PK and FK on Customer(code_contract)

    Coefficent's total number is 50.

    Each contract may have from 1 to 5 coefficients.

    a typical query may be:

    SELECT amount * coef_1 + amount + (amount * (coef_34 - coef_23))

    FROM Contract inner join Customer on Contract .code_contract = Customer.code_contract

    where code > @start_code and code <= @end_code;

    Now that I have explained this: What do you recommend?

    Thursday, November 21, 2013 2:19 PM
  • I would answer the same thing.

    You said it is an sql exam.  I assume it is in an academical situation.  If such is the case, you will probably be evaluated on whether you can follow theory as much as practice.

    Take time to read the article I provided.  It contains guidelines on how to perform normalization up to 3rd normal form as well as explanation on why those are recommended.  it even holds example of what happens if you don't follow normal form including the design you provided.

    Normalization is not a requisite, people sometime denormalize to achieve performance.  However you must understand the impact of your decision and be able to defend them.

    • Proposed as answer by Sofiya Li Friday, November 22, 2013 9:18 AM
    Thursday, November 21, 2013 2:43 PM
  • Hello Archan87,

    Use the second Table structure.  Tomorrow you may need 50+ Coefficent value or 5+ coefficients for each contracts. IF you select the 2nd option you don't need to change the structure of the table latter.


    Regards, RSingh


    • Edited by Ch. Rajen SinghEditor Friday, November 22, 2013 6:18 AM
    • Proposed as answer by Sofiya Li Friday, November 22, 2013 9:18 AM
    • Marked as answer by Sofiya Li Thursday, November 28, 2013 6:37 AM
    Friday, November 22, 2013 6:16 AM
    Answerer