2

I would like to ask couple of questions regarding my schema table design.

I have the following data coming from an external database where I do not have any control over it.

PROD_NO  PROD_NAME         COMPANY_ID   COMPANY_NAME    PROD_MARK_NO    PROD_CATALOGUE  PROD_TYPE   PROUDCT_EXPIRY_DATE
MXTP     PLANT_FERTILIZER   BRZL-98     XYZ CO.         MX-FRT          FERT-1          PERISH-01   30-09-2018 
RXLG     FARM_FERTILIZER    BRZL-102    ABC CO.         RX-FRT          FERT-6          PERISH-06   31-05-2016

User would like to verify the product information and add two more fields

  1. Verify_date
  2. Comments

How could I normalize the data I receive and put into relational tables?

PROD_NO, COMPANY_ID, PROD_MARK_NO,PROD_CATALOGUE and PROD_TYPE will be always unique.

  • Will there be multiple comments added over time or will this be one text field that describes the product? – James Anderson Jul 3 '15 at 8:23
  • @JamesAnderson One text field for one product, not multiple comments for one for product. – Jåcob Jul 3 '15 at 10:49
1

This schema would make sense to me:

tblCompany
(
  Company_ID  PK
  Company_Name
)

tblProduct
(
  Prod_No  PK
  Company_ID  FK_tblCompany_CompanyID
  Prod_Name
  Prod_Mark_No
  Prod_Catalogue
  Prod_Type
  Prod_Expiry_Date
  Prod_Verify_Date
  Prod_Comments
)

You could have separate tables for product type and product catalogue to get to 3NF but that's a decision for you to make depending on the amount of different values those columns could have and the way in which this data will be accessed (OLTP only or heavy reporting used here too?).

  • Do I need separate table for PROD_NO and PROD_NAME similar to Company_ID and Company_Name? – Jåcob Jul 3 '15 at 11:17
  • No. Tables should represent an object and it attributes. PROD_NO and PROD_NAME are attributes of a product so they belong in the tblProduct table – James Anderson Jul 3 '15 at 11:30
  • Is that because PROD_NO and PROD_NAME are not getting repeated? – Jåcob Jul 3 '15 at 11:34
  • Technically yes but really they are core attributes of a product, it really wouldn't make any sense for them to be in a different table. – James Anderson Jul 3 '15 at 11:35
  • It might make sense for product type and product catalogue to be in different tables as they could be duplicated many times in the product table. But you may think that's acceptable if you don't want another join in all the queries that need to see that data. Depends on your needs. – James Anderson Jul 3 '15 at 11:38

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.