Opinions on Master/Child relationships

[dan-cat]

Hi,

We all know we're not supposed to duplicate data right? So was wondering how people deal with the deletion of Master records.

For instance let's say I have 3 tables.
tblSales
tblSalesSub
tblProducts

tblSales - lists the sales summary - stuff like SaleId, SaleDate, SaleTotal
tblSalesSub - lists the products contained within the sale.
tblProducts - is the Master list of products storing the product specifics

Now to prevent duplication of data I should just store the productId in tblSalesSub then reference that to tblProducts with an innner JOIN of some kind.
But what if the Master record gets deleted? The reference is broken and NULL values are returned.

How do you deal with this?
Don't allow deletion of these records?But just hide them when deleted?
Duplicate data by storing the product title and description within tblSalesSub?
Tell the user - tough cheese you shouldn't have deleted it should you?
Any other ideas?

Dan

 

[FoFa]

Well, if you going to allow deletion of master data, then you should set up a cascade delete of the related child records.
Typically we do not allow deletions, we allow (many terms) inactivation of a record. Then the application usually does not show inactivated records unless you check a box or select inactive items etc.

 

[Pat Hartman]

But what if the Master record gets deleted? The reference is broken and NULL values are returned.

You can prevent this from being a problem by establishing referential integrity. You have a choice when you establish RI as to how to handle deletes of the "parent" record. If you specify Cascade Delete, all child records are deleted automatically when you delete the parent record. This eliminates the creation of orphans caused by deleting their parent. If you do not specify Cascade Delete, Jet will prevent you from deleting any "parent" record that has dependent children. Only "parent" records who are childless can be deleted. The final step to tie this up properly is to make the foreign key field in the child table required. This will prevent the creation of orphan records by adding child records which are not associated with a specific parent.

BTW your tblSales violates second and third normal forms because you are storing calculated values.

Your business rules should dictate whether "parent" records may be deleted. If you want "shadow" deletes, you can add an archive flag so that records that you don't want to include in current processing can be bypassed.

 

[The_Doc_Man]

The way I have seen this done before has to do with having a regular "update" cycle.

What happens is you set aside a day, weekend, whatever, when nobody uses the system. You do some archiving. You remove the things that you will archive. But it's not as simple as that.

First, before the critical time period:

1. You establish a list of master items you would LIKE to delete. This step depends on your business rules.
2. You search the child tables for child records of the targeted items.
3. You determine whether there is something about the child records that would prevent you from deleting them at this time. This step depends on your business rules.
4. You reduce the list of targeted master items so that no "keeper" child records are involved.
5. You establish a list of child records to go with the targeted master records.
6. Establish a table for the archived records. OR Establish some other storage medium and format for the archived records. E.g. spreadsheet, delimited text files, something in a binary format, etc.etc. This step depends on your business rules.
7. Establish queries that will archive the child and parent records to the archive medium/format as selected.
8. Establish queries that will remove the child and parent records from your main DB.

Now, on the critical day, earn your keep by coming in at odd hours.

9. Verify the connection / availability of the archive destination.
10. Backup the DB
11. Run the child and parent archiving queries. (If the archive destination does not involve a relationship for these archived records, order of archiving is immaterial. Otherwise, do parent first.)
12. Run the child removal queries.
13. Run the parent archiving queries.
14. Repair and Compress the DB
15. Backup the updated DB. Then (AND ONLY THEN) discard backup from step 10.

Now, how do you select records for archiving and deletion? (How do you "make the list" of targeted records?)

Start by adding a single Yes/No flag to every record (parent AND child), call it Archive, set it to NO as default.

Then, when you identify the parent record candidates for archive and delete ops, mark the flag YES.

Then, find the child records corresponding to the parent. Mark THEIR flags YES.

Then go through the YES-marked child records and identify keepers by UNMARKING (setting Archive flag back to NO).

Then go back through the parent records and, if any marked parent's children are set to Archive=NO, set the parent flag to Archive=NO.

THEN go back and make all child markers consistent with their parent markers. Propagate all parent Archive markers to their associated child records, whether YES or NO.

Now, the archive and delete queries I mentioned require you to only look at the marker flags. The archive queries can append marked records to your archive table or export such records to some other medium or whatever. The delete queries can delete the marked records.

Note that the MARK/UNMARK process seems a bit complex. The initial "MARK THE PARENTS FOR ARCHIVE" and "UNMARK THE KEEPER CHILDREN" queries are the ones that depend on your business rules. All other queries are simply copying the Archive flag from parent to child or from child to parent to assure consistency.

What this does is that it follows the rules of referential integrity and preserves your data per your business rules in a way that Access will support.

This can be extended to multi-layer relationships if you remember that when archiving, order either doesn't matter (no relationship within archive files) or else you archive OLDEST generation first. When removing, always delete the YOUNGEST generation first.