Mysql – Best approach to design a database model for this situation? Clients and records

database-designMySQL

We need to store the following information for each client:

Three types of record per file, for example (obviously, with real data):

HDR2007022399999900000000000Soft/XX        0.0  XXX        
XXX000000000000000               200702200000000000000000000           999999999               000000000100001
XXX003001958208255               200702200000000000000000000               999999999               000000000100001
XXX003001958208255               200702200000000000000000000               999999999               000000000100001
TRA200702222222222222222222XXX             0.0  XXX

In the name of the file is the client code which is used to identify each client.

The line starting with "HDR" is the Header.
Those starting with "XXX" are the Body and finally "TRA" is the footer.

Although there must be presented always together, at a first glance I decided to store them in three different tables, as they're different types of records (Don't know even if this is the best way)

I thought of two different models (can't post images):

With hierarchy: http://i.stack.imgur.com/ThHWn.png

I'm not sure if I will need a supertype field in the "Record" table in this case.

The other:
Without hierarchy: http://i.stack.imgur.com/FiJDf.png

Maybe I can remove idClient and just identify with the clientCode, as it is unique, but I have read is always good to have an auto incremental id.

I think the hierarchical approach is more correct, but not sure of that.

Also, with a hierarchical model it will be more difficult to retrieve all data, right?

The parameters I will have at first, to make queries are the dateReceived and clientCode. So if I want to retrieve all records for a client, I should use an INNER JOIN to join all subtypes, right?

Well, sorry for all questions.

Thank you very much in advance

Best Answer

You should organize the data according to its cardinality.

If you have one header and one footer per client, then these columns can be part of your client table. Since there are clearly multiple body records per client, these should be kept in another table.

If you can have multiple records per client, then your "with hierarchy" model is not bad, although it could be simplified to collapse the Header and Footer into Record since there is only one header and one footer per record.

Your "without hierarchy" model is less desirable. If you have multiple records per client it won't tell you which headers/bodies/footers go together. If you have only one record per client, then having separate tables for header and footer is overkill for the same reason as I noted above for the "with hierarchy" model.

This is what I would recommend, depending on how many records each customer can have:

ERD

Related Question