Multiple Referenced (Parent) Tables to One Referencing (Child) Table
I am attempting to model EDI documents. Each EDI document is comprised of a collection of segments. Each segment has a specific structure and may contain other segments, which in turn can contain other segments, and so-on. Furthermore, each segment (or "sub-segment") may or may not be repeated.
A very simplified view of a document might be the following, where segments are identified as "[segment-name(sequence-number)]":
[doc_control]
..Control_Number
..Sender
..Reciever
..[order_header]
....Order_Number
....Order_Date
....[comment(1)]
......Comment_Text
....[comment(2)]
......Comment_Text
....[order_detail(1)]
......Order_Line_Number
......Item_Number
......Quantity
......[comment(1)]
........Comment_Text
......[comment(2)]
........Comment_Text
......[comment(3)]
........Comment_Text
....[order_detail(2)]
......Order_Line_Number
......Item_Number
......Quantity
....[order_detail(3)]
......Order_Line_Number
......Item_Number
......Quantity
......[comment(1)]
........Comment_Text
I initially thought that as each segment has a specific structure, I could create a table for each segment. I would then use a foreign key relationship to establish the link between a segment and its sub-segments. I was doing great until I came to describing the foreign key relationship for the "comment" table. As you can (hopefully) see in the example above, the comment segment is a sub-element of BOTH the "order_header" and "order_detail" tables.
After scanning the MySQL manual, the web, and numerous SQL books, I have not found this situation addressed. I have found the common example of an address table being related to employee, vendor, and customer tables where the solution was to create a super-key. I don't think this applies to this situation.
The best that I could come up with is to include multiple FK columns in the comment table. In this example, I would need one for "orderheader_Id_FK" and one for "orderdetail_Id_FK". I would then populate one and only one of the FKs. The second solution is to include a FK column and a "foreign_table_Id_FK" column in the comment table. I would then need (?) to include a "table_id" in the orderheader and orderdetial tables.
Of these two solutions, the second one is a better fit for my situation as in reality, the comment table is related to many other tables and as new documents are added, this list will only grow.
My problem is twofold:
1) I do not think that either of my solutions are very good and I "know" this situation must be a classic case that has been resolved.
2) Assuming, that I use my second solution (FK_Row_Id + FK_Table_Id), I am not sure of the best way to implement this. Do I need to include the Talbe_Id as a column in the referenced table? Is there a way to default a column value to the table name ... or should this be an "Id" pulled from a master list of tables based on the table name (e.g. Table_Id = select a.Row_Id from TABLES a where a.Table_Name = ?)
Sorry for the long post. Any assistance will be greatly appreciated.
Thank you,
Brian