Normalization (Confusion) RRS feed

  • Question

  • Hi,

    In JobCreation table StockCode field exists. In JobRouting table i am using jobcreation primary key as foreign key.

    do i need to have stockcode field also in job routing ?

    Tuesday, May 7, 2013 7:02 AM


All replies

  • >do i need to have stockcode field also in job routing ?

    No, because you can JOIN JobRouting with JobCreation.

    In a normalized data, there is no data duplication.

    JOIN article: http://msdn.microsoft.com/en-us/library/ms191517(v=sql.105).aspx

    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    • Edited by Kalman Toth Tuesday, May 7, 2013 9:25 AM
    • Proposed as answer by Fanny Liu Wednesday, May 8, 2013 10:26 AM
    • Marked as answer by AngaraKiran Thursday, May 9, 2013 3:46 AM
    Tuesday, May 7, 2013 9:24 AM
  • No, as Kalman pointed  you can JOIN two tables on PK=FK and having StockCode in SELECT

    SELECT StockCode  FROM JobCreation JOIN JobRouting  ON.....

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Fanny Liu Wednesday, May 8, 2013 10:26 AM
    Wednesday, May 8, 2013 6:58 AM
  • I want to add a bit to what people are saying. With your two lines of requirements, and as you have agreed, the answer is no. But the question you have to ask first in these cases is: do they have the same meaning. So if the stockCode is the stockCode for the entire JobCreation (task?), and you just need that value when you are in the jobRouting table, then 100% correct, you don't need it, join for it.

    But make sure that it is 100% all of the time the same with NO overrides. If there is any variability, then this falls apart. Consider too what happens if the JobCreation.StockCode changes... What will that do to your data? Will previous steps now seem to have used the new stockCode? Does that matter?

    In all matters of normalization, the requirements mean WAY more than anything else when you are dealing with a design.


    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Tuesday, May 14, 2013 6:12 PM