1

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.

  • 1
    Hey Schlebe - you already have some solutions I see - have you tested them at all? Your question is very broad - I would suggest refining it down to a set of specific questions or you're unlikely to get a helpful answer. – George.Palacios Nov 28 '18 at 13:06
  • Might be a duplicate of dba.stackexchange.com/questions/193394/… – mustaccio Nov 28 '18 at 19:18
  • 1
    I have read the possible duplicate and vote up :-) But my questions is a little different. I'm waiting an answer that explains advantages and inconveniences of each solutions. – schlebe Nov 29 '18 at 8:12
2

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

  • I vote up for the effort to write an answer but I dislike solution 4 that is similar to PARAMETER table that we use where I work. This solution contains metadata in each rows and is always implemented using String type for any values. The management of new fields is perhaps simple, but I suppose that the selection of rows is complex because we need to define an LEFT JOIN for any fields in row ! But it is interesting. – schlebe Dec 1 '18 at 21:37

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.