CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-databasedata-modeling
Prefect73
Prefect73

Reputation: 341

Data modelling. How to model hundreds of dependent fields, 1:n or as one table?

i have to store leasing cars that come with graduated progressive running costs, for example:

main data: 

Id:    123  
Model: Ford Transit
yom:   2013

graduated variable running costs:

kilometers/year utilities  tires service misc
        10,000        80     400      50   30
        12,000       100     400      55   35
        14,000       120     400      60   40
          ...
       100,000       500   1,500     150  100

So with a scale of 10,000 to 100,000 in steps of 2,000 and 4 different cost types, this results in 50 x 4 = 200 data fields.

Is it better to model this as a 1:n relation or is it ok to have more than 200 columns in my car table (MySql)? What pros and cons do exist?

In case of 1:n, would a trigger make sense to create the 200 fields in the variable costs table whenever a new car record is entered?

thanks in advance for any kind of hint

Upvotes: 4

Views: 942

Answers (2)

PerformanceDBA
PerformanceDBA

Reputation: 33728

So with a scale of 10,000 to 100,000 in steps of 2,000 and 4 different cost types, this results in 50 x 4 = 200 data fields.

Yes. When that is Normalised, the 200 fields per record are transformed into 4 columns per Normalised row.

Is it better to model this as a 1:n relation or is it ok to have more than 200 columns in my car table (MySql)?

Well, if you are considering relations, it is a 1::n relation.

It is never acceptable to implement un-normalised records (eg. in your case, one of 200 fields) in a database.

What pros and cons do exist?

I can't give a tutorial here, but in general, Normalised rows have (a) Relational Integrity, (b) Relational power [you will notice that in the form of reduced joins], (c) Relational speed (d) ease of extension [addition/change to the database], and (e) ease of coding. None of which un-normalised records provide, they provide the opposite.

In case of 1:n, would a trigger make sense to create the 200 fields in the variable costs table whenever a new car record is entered?

In general, triggers are not required in Relational databases. They are only required when one needs to do weird things, and weird things are the result of not Normalising the data.

In your case, the 200 values for the new car being added are not known, so a trigger wouldn't work anyway.

No, do it properly, in an ACID Transaction, as and when the user adds a new car, and populates the four columns per kilometre step. You may well have 4 default values that auto-fill the fields on the screen, but that should not be saved to the database until the user hits the Add button..

Data Model

Here is the data model that you need.

  • Vehicle Maintenance Data Model

  • I have Normalised the data, without explanation of the steps or process, this is the end result.

    • Eg. Ford Transit is not Atomic, I have made it Atomic by separating Manufacturer and Model.

  • Mandatory/Optional costs:

    • If the four cost fields appear for every Maintenance (Kilometre step) row, you need the model on the right

    • If any of the cost fields are optional, you need the model on the left. I have assumed that Service is mandatory, ie. it occurs for every kilometre entry.

  • I have given you Relational Keys, and thus Relational Integrity. If you do not use compound keys, you will lose that integrity. Eg:

    • A Model (eg. Transit) does not exist independently, it exists only in the context of a Manufacturer (eg. Ford).

    • A ModelYear (eg. 2013) does not exist independently, it exists only in the context of a Model (eg. Ford Transit).

    • You may substitute the names codes, if you think they are too wide.

  • The Maintenance table needs a simple CHECK constraint to ensure that the Kilometre step is modulo 2000.

That is an IDEF1X model. IDEF1X is the Standard for modelling Relational Databases. Please be advised that every little tick; notch; and mark; the crows foot; the solid vs dashed lines; the square vs round corners; means something very specific and important. Refer to the IDEF1X Notation. If you do not understand the Notation, you will not be able to understand or work the model.

Record Id

Note that these days many people do not understand how to create Relational tables, with unique rows, as required by the Relational Model. They create files, typically with a Record ID each, and allow masses of duplicates. Please read this Answer, from the top to False Teachers. And try the code given, and linked, therein.

Warning

Zohar:
I would also suggest a lookup table for car models, so that each row in the cars table would have a model id instead of the model name.

That sort of advice comes from people who have read the books of false teachers, and who apply it, and advise it, with no actual knowledge or experience whatsoever. It keeps everyone stuck in pre-1970'2 ISAM Record Filing Systems, while thinking they have a "relational database".

They simply do not know the Relational Integrity, Power, and Speed, that they are missing. If someone asks a question, I will provide an answer. Not here, this answer is complete.

Upvotes: 3

Carl Prothman
Carl Prothman

Reputation: 1551

You could go with a data model which has pre-populated look up tables (right side). Then apply that maintenance to the actual vehicle (left side) ERD

Upvotes: -1

Related Questions