0

I have a question regarding database table design.

I have a product master table which has all the details of a product.

User would be adding notes against each product (product number) and for each product there would be multiple notes.

Proposed columns for notes against product.

Subject
Description
Entered BY
Updated By
Entered Date
Updated Date.

Should I create two tables, one to keep the product no and another table to keep the notes and the other columns or one table to keep the product no and other columns?

closed as off-topic by Jack Douglas Jan 16 '17 at 19:35

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the question.

1

If you use only a table, how would you keep several notes? You would fail further normalisation, by having repeating attributes, and you would either need a nightmarish dynamic structure (implemented in application code, to make it worse) or an almost equally nightmarish limit on user notes.

You do not even need a surrogate key. Product number seems a decent natural key already for the product table, and a composite natural key for the notes table seem easily feasible, for instance (Product number, Entered by, Entered date).

  • If I create a ProductNoteId to have a unique number for each row, would it compromise the normalization? – Jåcob Jan 22 '17 at 7:14
  • No, it would only redund over the natural key. It would hurt performance, waste memory and storage and make the model more hard to understand, but not because of lack of normalisation. – lfd Jan 22 '17 at 18:47
1

Two tables, Product and Product Notes. Link them by ProductId, primary key of your Product table.

  • What all columns the product table will have? – Jåcob Jan 16 '17 at 18:54
  • ProductNotes will have: ProductNoteId (PK), ProductId (FK to Product) plus your columns (Subject, Description, Entered BY, Updated By, Entered Date, Updated Date) – Andrei Hetel Jan 16 '17 at 18:56
  • So there can be multiple ProductId and ProductNoteId will be unique right? – Jåcob Jan 16 '17 at 19:00
  • ProductId is primary key (unique) in Product table. ProductNoteId is primary key (unique) in ProductNote table. ProductId is not unique in ProductNote, because as I understand from the question for 1 product you can have 1 or more notes. Hope it helps. – Andrei Hetel Jan 16 '17 at 19:03

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