Schema For Varying/ Optional Parent Tables

[Dal]

Basically I have a tBill as a parent which has 5 children:

• tSubBills
• tMainHeaders
• tHeaders
• tSubHeaders
• tComments

All of which possible feed the grandchild tBillItem. There will not be a great consistency in how the tBillItem is populated. Sometimes they will have a tSubBill parent, sometimes there will be no tSubBill parent. Sometimes there will be a tMainHeaders parent, sometimes not.

I want the tBillItems to be linked to their parent. I do not want to have to use the Ref column in a query later... How can I acheive this in good practice to not violating Normalization rules...?

This is pretty simple to what I need to do later; it seems I do not understand relationships fully... Anyone have any links to advanced relationship tutorials. The ones ranking on google are absolute basics it seems.

[Dal]

Sorry should have mentioned - I'll be copying the records a lot so I'm also trying to limit the manual work by letting Access copy sub records for me. Be interesting to see how related fields in other tables are copied or if they need to be done by individually by a query of some sort.

[CJ_London]

Aside from not understanding what you are trying to achieve since no example data provided, I don’t understand your concern - either there is a child or there isn’t. If there isn’t, then a child record does not exist.

I suspect that subheader should be a child of headers which in turn should be a child of mainheader unless you are saying you can have a sub header without a a header. And I don’t see why you need multi field primary keys.

[Dal]

unless you are saying you can have a sub header without a a header. And I don’t see why you need multi field primary keys.

Thanks, sorry for the ambiguity. Yes you are right, you could have a sub-header without a header & likewise for the other four.
BillItems could be under any of the 5 parents. It seems I cannot link the 5 fields to the tBillItems because the BillItem record will have different parents & to have empty fields would violate 2nd normal form I think/ or 3rd normal form.

Maybe the below is the way to go?

I have multi-field primary keys because there are other tables linked which from the tutorials I've seen they recommend this. A unique set of primary keys is produced from the fields of other tables.

[ArviLaanemets]

So you have some items you are buying or selling. I'll assume you may buy/sell same item more than once, so as start you need an Items table (in square brackets are possibly optional fields - depending your data)!
tItems: ItemID, ItemName, [ItemType], ItemUnit, [ItemPrice] (You can have ItemPrice field here, in case prices never change, otherwise you need an additional table where you register the price history for every item, or you register the price manually with bill row)
In case you buy/sell also something very different from physical items (e.g. services), you may be need a separate table(s) for them too;

Then you need the table where you register your bills.
tBills: BillID, [ClientID/SupplierID], [TenderID], [BillType], BaknkID, BankAccountNumber, BillDate, BillComment, ... (In case you will have bills of different types - e.g. purchase or sales bills - you need BillType determined as Foreign Key. It also will determine, are purchased/sold quantities handled as positive or negative numbers. BillID will be a Primary Key, all other keys will be Foreign Keys - and you need a registry tables for those also);

Then you need a table forSubBills (or BillRows what I would prefer).
tBillRows: BillRowID, BillID, ItemID, ItemQty, [ItemPrice],[BillRowComment], ... (BillRowID will be PK, BillID and ItemID will be FK. In case you don't have permanent prices registered in Items table, or prices history registered in separate table, you can have here ItemPrice field for the price of item for this Bill too);

I can't understand your logic for this bunch of various headers specifically for bill rows - so no comment about them.

[NoellaG]

A short description of the business processes could help a lot.

[CJ_London]

you could have a sub-header without a header & likewise for the other four.

in that case why have multiple tables with the same fields - you just need the one, perhaps with a field to indicate what type of record it is (header/subheader/etc), might also want to use a more generic name for subBill/Mainheader/subheader/comment

I have multi-field primary keys because there are other tables linked which from the tutorials I've seen they recommend this. A unique set of primary keys is produced from the fields of other tables.

who are 'they'? Using multiple fields as a PK can lead to big issues down the line when trying to link tables. By all means have a unique index across multiple fields where the index is set to not allow duplicates - but don't use as a PK.

As others have said, unless you can clarify what you are attempting to do, we are just guessing at it's purpose and what would be a correct way forward which is a waste of everyone's time

[isladogs]

Anyone have any links to advanced relationship tutorials. The ones ranking on google are absolute basics it seems.

You could try my 3-part series of articles on Relationships & Referential Integrity starting with https://www.isladogs.co.uk/relationships1/