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

  • 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 '12 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 '12 at 17:35
  • What do the records represent? – Neil McGuigan Jul 12 '12 at 18:25
  • They represent data like phone numbers, control codecs, etc – aesptux Jul 12 '12 at 19:31

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 '12 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 '12 at 20:03

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.