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