Unanswered: Table design and information duplication
I have a table called “tblPARTS”. We keep all parts used by our company in this table. We categorize these parts using a field called “PART TYPE”. One of these part types is “PLANTS”. Although we need to treat plants as parts, we also need to add extra fields to this part type like “WATER REQUIREMENTS” which will be needed by installation team later. And because this extra field will be only applicable to “PLANT” type parts, I thought that I will need to create a new table “tblPLANTS”, then to transfer and synchronize all “PLANT” type parts in “tblPARTS” table with “tblPLANTS” table. This way if I need to process plant water requirements I will refer to “tblPLANTS” table, and if I want to add plants as parts I will use “tblPARTS” table.
Is this information duplication? Is this an acceptable design?
I would just add an extra column to tblParts that will show extra information like Needs Watering, Keep Dry, Requires annual rust inhibitor, etc. On reports, simply expand the height that field if it is not null.