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: https://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: https://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

  • I am not sure how you will use the final database, but why not store your data in 1 table? I know it is not a normalized solution, but how does that affect your case?
    – NoChance
    Jul 12, 2012 at 16:46
  • Well, it could be a solution, although as you say is not normalized.... Maybe two tables, clients and completeRecord. 1-N relationship, and I can query the same way, with clientCode and dateReceived
    – aesptux
    Jul 12, 2012 at 17:35
  • What do the records represent? Jul 12, 2012 at 18:25
  • They represent data like phone numbers, control codecs, etc
    – aesptux
    Jul 12, 2012 at 19:31

1 Answer 1


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:


  • It's also important to know if there can be multiple records per customer or multiple customers per record - if two related clients can share a footer it would make sense to maintain a single copy of that instead of one per client record.
    – JNK
    Jul 13, 2012 at 13:31
  • Thank you all for your answers. Finally I have decided to implement "Multiple Records Per Customer". So far so good, did not find any flaws on the model. Thank you again.
    – aesptux
    Jul 16, 2012 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.