Making record updates that affect multiple tables in different ways

[ScubaBart]

Still on the Apiary / Hive / Honey Bee project for those of oyu that have helped me along the way and I have gotten to what is arguably going to be the most complex operation.



So, a quick synopsis on the organization of beekeeping. When you have Bee Hives, it is generally called an Apiary. So, a bee keeper may have multiple Apiaries in different locations with each Apiary having some number of Hives. Easy enough.

Hives get inspected on some schedule that suits each bee keeper. Typically, all or most of the Hives in a given Apiary will be inspected in the same visit. While certain aspects of the Inspection are common (Specific Apiary, Date, Weather, etc.) many other details of the Inspection will be specific to each individual Hive. So, to put this in visual terms, here is the relationships I have presently for this connection of data.



The highest level table is the T_Log_Apiary. There are Many Hives for each Apiary and there will be Many Inspections (over time) for each Apiary. Then, you have the T_Log_Hive table. There will be Many Inspection Details for each Hive (over time). And lastly, you have the T_Log_Inspection_Main table that records the common Inspection data. There will be Many Inspection Details (one for each hive in Apiary) for each Main Inspection record.

So, hopefully this makes sense and maybe you have already foreseen some of the issues that can occur here that will be challenging to address.

Certain changes are relatively simple but there is one action that makes updating the records very complex. Occasionally, a bee keeper will move a hive from one Apiary to another. The catch here is that you want the inspection details And the common inspection data to follow the Hive. So moving a Hive will involve changing data in three tables and likely force a new record in one.

For example, Say I move Hive # 10 from Red Apiary to Blue Apiary. Moving just the hive is easy enough and the link to the Inspection Details remains intact as the only change in the Hive table is to the value of the Link_to_Log_Apiary_ID to connect to Blue Apiary. However, while the Inspection Detail record will still be linked to the specific Hive Detail record, that Inspection Detail record will also be linked to the Inspection Main record that connects with Red Apiary. There will not be a corresponding Inspection Main record for Blue Apiary. In order to make the transfer complete, I would need to have a new Inspection Main record created for every Inspection and link them to Blue Apiary and copy the data from the Red Apiary Inspection Main records to the new Blue Apiary Inspection Main Records. Then change the Link_to_Log_Inspection_ID for each Inspection Detail record will have to be updated to connect with the new Inspection Main records. That will then maintain data integrity.

So, in my mind, the process would be something along the lines of open record for Hive # 10. Use Combo-box to select alternate Apiary. Upon accepting choice, change Link_to_Log_Apiary_ID to match Log_Apiary_ID of new Apiary. Pull every Inspection Record for Hive # 10 (likely a Query that contains the rows of both tables) and by selecting each row that has the correct Link_to_Log_Hive_ID for Hive # 10, duplicate the data from each Inspection Main row while in tandem, changing the Link_to_Log_Inspection_ID in the Details table to match the new Main table, respectively. The order of these operations may not be right for coding. Easy Peasy, right ?

An alternative would be to make copies of the appropriate Inspection Main and Inspection Detail records, link them to the correct Apiary and then delete the originals but to me that doesn't sound any easier.

The very easy way to address this would be to combine the two Inspection tables into a single table but then there is a fair amount of information that is duplicated and the whole intent of relational databases is to eliminate duplication of data, right ?

I put this in the Programming forum because to accomplish what I have laid out, it will take some coding well beyond my skill set.

If it can be done, I need some coding training or examples. Currently I am just getting barely proficient on the opening and closing of forms and setting values. Something as advanced as this is simply outside of my range.

Ready for all sorts of input.....

[Welshgasman]

Moving a Hive from one Apiary to another, should just be changing the LinkToLogApiaryID.
Then when you next have an inspection the new value is used.

I do not believe you should have multiple paths to the tables. You should have only one path.

So to get to InspectionDetails, you would go from Apiary to Hive, to Inspection to Detail.

If you need to keep a history, you would store the value at that time.

Think of an invoice detail line. It will have a price and a discount. You do not want to link to the price for the product as if you have to reproduce that invoice a few weeks later, you would see the new price as it has changed since the sale. If you store that price at the time of the invoice, it will always be the same. That is not breaking normalisation.

So I think your structure needs to change to accomodate this. You do not move stuff around, you change the odd field here and there. Whether you need a history of the change is another issue.
So your system, just needs to have the ApiaryID changed in the Hive table. That should be it I believe, but the structure needs to accomodate that.

Then if you produced a report, you would see inspections for the hive when in ApiaryID1, and then in ApiaryID2.

A lot of thought needs to fo into a DB as to what you want out of it. That generally defines what goes into it and how it is structured.

You have been concentrating on the presentation of the data, and have had to be persuaded to do it by another method on occasion, that works with how the structure should be.

Sadly, I am not that great at normalisation. I have to work with something, then modify it as I find fault with it. I cannot do it up front all in one go.

[ScubaBart]

.........

A lot of thought needs to fo into a DB as to what you want out of it. That generally defines what goes into it and how it is structured.

You have been concentrating on the presentation of the data, and have had to be persuaded to do it by another method on occasion, that works with how the structure should be.

Sadly, I am not that great at normalisation. I have to work with something, then modify it as I find fault with it. I cannot do it up front all in one go.

I understand what you are saying and I have been trying to step back from this and take a broader look. I am a detail person and sometimes building rom the bottom up causes issues as I am running into now.

I may indeed re-arrange the relationships. I can probably maintain a link between the Apiary table and the Inspection table with a common field but not necessarily have the connected relationship. As you say, the history will still be there because of the common field.

I think I will re-evaluate the operations by eliminating that one relationship and see where that takes me. Note that I have a field in the Inspection Main table for Apiary_ID_(maybe_delete) that I had expected to get rid of but perhaps it is what I need after all.

This goes in line with a previous post I had put up concerning updating text fields to automatically add a text line for when changes (such as moves) were made and there were several suggestions to construct a Revisions table to record those changes. The same approach would address that scenario as well.

It adds a little effort to the navigation of the information but it should still be simpler to code overall.

Will work on that over the next few days as time permits and report back.