This is a "theoretical" question linked to quality of table's definition.
I work on tires and, currently, I have a big table that, for each tire's label, contains a row with label's values. For more information go to this site: DUNLOP Tire Label Explained.
The used tire's labels are Rolling Resistance ('RR'), Wet Grip ('WG') and Noise ('NOISE').
Some columns in this big table are common to 'RR', 'WG' and 'NOISE' labels, but some other columns are specific to only one label. For example
CTC LABEL GRADE G-SYMBOL G-VALUE AVERAGE ------ ----- ----- -------- ------- ------- 345674 RR B 11,2 459864 WG A 2,1 893023 NOISE )) 71 71,2
CTCcolumn is a term used to group all tires having same characteristics and belonging to same tire's line's name. Example: 'Goodyear EAGLE 155/60R20 95H Extra Load'.
LABELcontains the shortname of the label.
CTC+LABELrepresents the table's primary key.
GRADEcontains the grade (level) of the label. For 'RR' and 'WG' the grade can vary from 'A' to 'G' where 'A' is the best grade. For 'NOISE', the grade is defined by a symbol and not a letter.
G-SYMBOLhas been added to represent the grade of 'NOISE'. Acceptable values are ')' or '))' or ')))', where ')' is the best value for 'NOISE'.
G-VALUEhas been added to represent the grade of 'NOISE'.
AVERAGEis the average of all measures taken on some tires that are used to fix the grade of all tires belonging to same tire's line's name.
As you can see,
GRADE is only used by 'RR' and 'WG' and
G-SYMBOL/G-VALUE are only used by 'NOISE'.
AVERAGE column are used by all labels but magnitude of value for each label are distinct. For 'RR', values are between '6' and '15' kg/T, and for 'NOISE' values are between '60' and '80' dB.
How many tables must be defined to manage all these fields?
Solution A: 1 big table?
BIG-TABLE: CTC / LABEL / GRADE / G-SYMBOL / G-VALUE / AVERAGE
Solution B: 3 tables (one in label)?
RR: CTC / AVERAGE / GRADE WG: CTC / AVERAGE / GRADE NOISE: CTC / AVERAGE / G-SYMBOL / G-VALUE
Solution C: 4 tables (one with specific field in label + 1 for common fields)?
RR: CTC / GRADE WG: CTC / GRADE NOISE: CTC / G-SYMBOL / G-VALUE COMMON: CTC / LABEL / AVERAGE
Solution X: another solution?
What are the advantages and inconveniences of each solution?
Some other considerations:
It is very uncommon to mix ALL labels.
In the future, some new labels (as 'SNOW') will be added.