What are the advantages and inconveniences of generalization or specialization of SQL tables?

Question

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

... where:

• CTC column 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'.

• LABEL contains the shortname of the label.

• CTC+LABEL represents the table's primary key.

• GRADE contains 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-SYMBOL has been added to represent the grade of 'NOISE'. Acceptable values are ')' or '))' or ')))', where ')' is the best value for 'NOISE'.

• G-VALUE has been added to represent the grade of 'NOISE'.

• AVERAGE is 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.

Question(s):

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.

Answer 1

Solution A is a bad solution if you have to add columns in future. Using alter table in production environment is discouraged. Can hold the database because of locks needed. This could not be a problem, but it really depend on the number of rows and concurrent transactions. On the other hand is very simple to retrieve data, is a sort of denormalization, so no join imply fast response time.

Solutions B and C are two similar desings. I dont like them. You wont be able to have cumulative data (count(1), group by..) in a single statement. You still have the problem above, if you need to add columns. But now this problem is repeated 3 o 4 times, compared to solution A. Not good.

There is a 4th solution, using a table for key/value. In this scenario you have a big table whith only the common columns, and a second table whith the following fields:

id / bigtable_id (references bigtable primary key) / key / value

Key column will store fields which apply only to certain type of tire coupled whith its value. For example there could be rows like:

id ==> 1 /bigtable_id ==> whatever needed /key ==> grade /value ==> A

id ==> 2 /bigtable_id ==> whatever needed /key ==> g-value /value ==> 71

There is an one-to-many relationship between tables. This is the solution I have seen most of the time. Is simple to manage, no need to alter table: if you need a new field just add new record/records.

In my opinion the fact that mix label is uncommon, is a secondary issue in design. If the problem is the number of rows, you can use partitions and subpartitions, but I prefer have the DB desing in order. Of course this is only my way to see order ;)

my 2 cent