Question about best practice on overwriting records vs deleting records

[Pat Hartman]

Yes, I meant unique indes.

We have gotten off track here. I thought there were originally THREE fields that when combined, constituted uniqueness.

 

[Benginner2212]

Yes, I meant unique indes.

We have gotten off track here. I thought there were originally THREE fields that when combined, constituted uniqueness.

No, there were only two fields that are combined to determine uniqueness. Up to this point I haven't concatenated the two fields. I have added a field labeled fullCableNumber that will be a concatenation of the cableCategory_PK and the cableNumber fields. Then I will be able to use the fullCableNumber field as the unique index for the first index and then I will create an AchiveDT field on the table that I can use as the second non- unique index.

When I archive the record, I can null out the cableCategory_PK and the cableNumber Fields. After that I just need to figure out how to find the gaps between cable numbers in each category.

 

[Pat Hartman]

fullCableNumber field as the unique index for the first index

Which ever field you use as the unique index, needs to be nulled when you archive the record. the data is still in the other working fields. This is a hokey solution and I don't like it even though I suggested it. It was a random thought but without the database and application in front of me, I am not convinced it is the best solution.

Maybe it is better to create a child table. I don't like removing the values of fields to implement this. I thought that reuse happened when you connected the cable to something else

 

[Benginner2212]

I thought that reuse happened when you connected the cable to something else

I am sorry, I may have been using some terminology incorrectly. There are two situations where we would reuse a cable number. The first situation is just like you described, HDV2202 is connects our old digital beta tape deck to input 279 on our video router. Then the 20 year old digital beta tape deck bites the dust and we have to replace it with a new server that records everything to an SSD hard drive. After I have gotten the new server up and running, I would go into the database and pull the record for HDV2202 and I would edit the record to reflect the changed that were made. So if the old digital beta deck was in rack 13, but we installed the new server into rack 12, I would change the source rack from 13 to 12. Then I would change the source description to note HDV2202 is now connected to the new server and not the old digital beta tape deck.

The other situation where we would reuse a cable number is after equipment has been disposed of and the cables have been removed from the rack and this is the situation that I am not sure if it would be better to delete or archive the record. We remove the old digital tape deck along with the cable connecting the old digital tape deck to the router so now there is no tape deck connected to router input 279 via cable HDV2202. The record for that cable is deleted and we print out new hard copies of the cable numbers. The hard copies now go HDV2199, HDV2200,HDV2201 and HDV2203.

Six months down the road we have to connected a new studio camera to the video router. We look in the database and see that there is not HDV2202 in the database and we decide to reuse HDV2202. The way that m y current database is set up, the first record for HDV2202 has been deleted so we create a new record the reflects HDV2202 connects Camera 5 in Studio A to Router Input 135 and print out a hard copy of the cable information.

I am not to worried about the first situation because, with the exception of the cable category, cable source rack and cable destination rack, every field on the cable information table is either a short text, date/time or a check box. So I am just changing the string of characters in the source description field on the cable information table.

The second situation is the one that I am most concerned about. If I keep just deleting the records I am afraid that my database will be corrupted at some point and we'll lose a bunch of data. But if I archive the record, I have to make sure that the archived data doesn't interfere with the new data and I am not getting duplicate records when I print a hard copy of the information or when I run a search for a cable number.

I have attached a copy of my database in case that might help.

 

[Pat Hartman]

I can't make any of these decisions for you. I've given you my opinion. If there is a paper trail (or email or whatever), I would not delete or overwrite the data. I would always want an audit trail. Here's a simple real world example. You have an order entry system. A customer adds 3 items to his order. He then deletes item 2 before going to checkout. Should I archive item 2 or just delete it since it never really happened. Likely as not, I would allow a delete in this case but once the order had proceeded to checkout, now the customer has seen the completed order and whether he printed it out or not, he might have so if at this point he goes back to his order entry and asks to delete item 2, I would now choose to archive it rather than delete it because there could be a reference to the item in the real world and I would need to be able to answer the question of what happened to item 2 should the customer call and say why did my order contain only two items when I ordered three.

 

[GPGeorge]

In my mind it comes down to this: You created a database application in the first place in order to keep track of transactions involving your products and services. Deleting or overwriting records means you no longer have any idea what really happened in the past. You've turned your database into a glorified spreadsheet.

If you do not care what happened last week, last month, last year or at any prior point in time, it's a moot point, I guess. However, personally, I'd look into replacing it a spreadsheet that would be easier, ironically, to update.

 

[Benginner2212]

I can't make any of these decisions for you. I've given you my opinion. If there is a paper trail (or email or whatever), I would not delete or overwrite the data. I would always want an audit trail. Here's a simple real world example. You have an order entry system. A customer adds 3 items to his order. He then deletes item 2 before going to checkout. Should I archive item 2 or just delete it since it never really happened. Likely as not, I would allow a delete in this case but once the order had proceeded to checkout, now the customer has seen the completed order and whether he printed it out or not, he might have so if at this point he goes back to his order entry and asks to delete item 2, I would now choose to archive it rather than delete it because there could be a reference to the item in the real world and I would need to be able to answer the question of what happened to item 2 should the customer call and say why did my order contain only two items when I ordered three.

I am not trying to get you to make the decision for me. I am simultaneously trying to weighs the pros and cons as I see them and figure out how to code this. Having an archive of the old cable numbers would be beneficial.

 

[Pat Hartman]

We are 47 posts into this. You seem to agree that having the archive is the correct approach. Now it's a matter of details. As I said, I don't like the idea I threw out of keeping two versions of the user key in the record. I think there is a better way and the better way would be to create a child table. Post a picture of your schema so we can move on to that.

 

[LarryE]

We are 47 posts into this. You seem to agree that having the archive is the correct approach. Now it's a matter of details. As I said, I don't like the idea I threw out of keeping two versions of the user key in the record. I think there is a better way and the better way would be to create a child table. Post a picture of your schema so we can move on to that.

Pat:
Although I have not been participating, I was curious myself, so here is the design. I created it from the attachment in post #44.

He is using RI, but is not cascading anything. The db has only a few thousand records. I can't believe "bloating" would ever be an issue. He should probably just APPEND records he wants to archive to an archival table, then delete the record in tblCableInformation and forget it. You still have an archived record and could then still reuse the CableNumber field in other connections. He said he was worried that deleting records might cause some sort of corruption, but I don't know why he believes that. Passing it back to you.

 

[Benginner2212]

He said he was worried that deleting records might cause some sort of corruption, but I don't know why he believes that. Passing it back to you.

There was another access forum that I used to be active on where I was told that when you deleted a records that have a relationship with records on another table its possible the relationship between the two records might not be deleted correctly. And that could potentially corrupts the database.

 

[Benginner2212]

We are 47 posts into this. You seem to agree that having the archive is the correct approach. Now it's a matter of details. As I said, I don't like the idea I threw out of keeping two versions of the user key in the record. I think there is a better way and the better way would be to create a child table. Post a picture of your schema so we can move on to that.

We are using this database to track equipment maintenance and equipment problems as well as cable numbers. I isolated the relationships for the cable number portion of the database.

 

[Gasman]

No point in having a relationship then?

 

[Pat Hartman]

It seems like Category, CableType, and CableNumber get assigned when a cable is added.

Then Source and Destination Rack get assigned when the cable is installed somewhere.

What does ActiveCable mean?

It also seems like Source and Destination Rack could change if you move a cable.

Can DrawingUpdate change after the record is created?

My original thought was that FullCableNumber would be filled in only when the cable was connected, but that won't work. I think that we need a history table instead. So,

1. Create a history table to mirror the information table. The existing PK becomes just a data field. Then you add a date and add a new autonumber PK.
2. When the any of the data fields gets changed, add a new record to the history table.
3. If the cable is "available" but not in use, the Source and Destination Rack are set to null but the CableType and the CableNumber remain.

As long as a cable is available, you should not be able to change the CableNumber. It is only when a cable is permanently out of use that the number would be available for reuse. At this point, you could set the CableType and CableNumber to Null and then add the concatenated CableType and CableNumber to the FullCableNumber

Then you create a new record with the CableType and the DMax +1 CableNumber. If this number exceeds 9999, you then need to find the lowest available CableNumber for the given CableType.

So, you have any changes to each instance of FullCableNumber stored. You reuse the parent record as long as you need to, creating a history record any time you have to modify the connection or Drawing information. Eventually, the cable with that autonumber ID will go out of use. The history stays tied to the original autonumber. Then the new use of the Type/Number gets assigned a new autonumber and the retired record just stays in the information table as a parent for the history.

Somebody else needs to also think about this. My head is spinning.

 

[LarryE]

There was another access forum that I used to be active on where I was told that when you deleted a records that have a relationship with records on another table its possible the relationship between the two records might not be deleted correctly. And that could potentially corrupts the database.

If you check the Cascade Delete Related Records box when you create the relationships, then ACCESS will delete the cooresponding records correctly.

 

[Pat Hartman]

There was another access forum that I used to be active on where I was told that when you deleted a records that have a relationship with records on another table its possible the relationship between the two records might not be deleted correctly. And that could potentially corrupts the database.

That is incorrect information. The person who posted that advice didn't understand the situations where Cascade Delete was appropriate. The problem is NOT with the database engine. The problem is with people who don't use the functionality correctly. It is similar to advice that cars kill so you should never drive one.

CascadeDelete is only ever used when a relationship is hierarchical and it is not used on all relationships because there are cases were you simply do not want to allow records with related records to be deleted.

For example OrderHeader --> OrderDetails is hierarchical. OrderDetails has no meaning outside of their relationship to an Order. The FK to OrderHeader will always be required and the default will be Null, never zero. If your business rules say you can delete an order, it makes absolutely no sense to keep the OrderDetails, so you would check the cascade delete box. Then, if you delete the OrderHeader row, all related OrderDetails will also be deleted. Clean, easy. But, life isn't that black and white. If the order is cancelled, maybe it makes sense to delete an order but if an Order has been shipped, it makes no sense to delete an Order so your business rules need to be clearly defined so you know how to have the database engine help you rather than crush you by deleting data that should never be deleted.

In the case of something like a StateCD lookup table, the Relationship between StateCD and OrderHeader isn't really hierarchical and the State table isn't the parent of the OrderHeader table even though the database engine implements them the same way. First of all, you probably don't want to ever delete California (even if you think the country might be better off without it). And even if you decided that it was OK to delete a record in the State table, you would never want to cascade the delete to delete all the orders.

I'm not going to discuss this further unless you have specific questions. The point is, Cascade Delete is a powerful tool that you should absolutely use when it is appropriate, but you need to avoid the tendency to look at every problem as if it were a nail just because you have a hammer in your hand.

 

[Benginner2212]

It seems like Category, CableType, and CableNumber get assigned when a cable is added.

Then Source and Destination Rack get assigned when the cable is installed somewhere.

What does ActiveCable mean?

It also seems like Source and Destination Rack could change if you move a cable.

Can DrawingUpdate change after the record is created?

Yes, Category, CableType and Cable Number are assigned when a cable is added. The source and destination get assigned when a cable is installed somewhere and the source and destination racks can be changed if the cable gets moved and installed somewhere else.

Activecable was my initial attempt to signify that a cable was still physically present between the source and destination racks but that the cable was not connected to anything. And I also used it to make sure that when we printed out a report of the cablenumbers that we didn't have those unconnected cables in the printout.

 

[LarryE]

It's been a few days now. Have you resolved your issues?

 

[Benginner2212]

It's been a few days now. Have you resolved your issues?

There have been some other more urgent issues that have cropped up the last couple of days that have prevented me from working on this problem. I am hoping that I can get those other issues resolved so that I can turn my attention back tp to this problem.

 

[Benginner2212]

I was able to find some time to work to on my database. I have created a table that I will be using to keep the history of cable number when the cable is decommissioned or other information is changed. And I have created an append query that updates the archive table.

Now the issues that I am having is getting executing the append query without having to open the query.

Currently I am using the

CurrentDb.Execute "apQryCableHistory"

to execute the query, but I get an error message about missing a parameter. Which I think is related to the way that I have the WHERE condition setup for my append query.

The SQL code for the query is

INSERT INTO tblCableHistory ( cableCategoryHst, cableTypeHst, CableNumberHst, cableSourceRackHst, cableSrcDescriptionHst, cableDstHst, cableDstDescriptionHist, drawingNumHst, dateAddedHst, miscCableNotesHst, associatedNotesHst, fullCableNumberHst, CableHistID )

SELECT [n] AS Expr1, tblCableInformation.cableType, tblCableInformation.CableNumber, tblCableSourceRack.cableSourceRack, tblCableInformation.cableSouceDescription, tblCableInformation.cableDestination_PK, tblCableInformation.cableDestinationDescription, tblCableInformation.drawingNumber, tblCableInformation.dateAdded, tblCableInformation.miscCableNotes, tblCableInformation.associatedNotes, tblCableInformation.fullCableNumber, tblCableInformation.CableInfoID

FROM tblCableSourceRack INNER JOIN (tblCableDestinationRack INNER JOIN (tblCableCategory INNER JOIN tblCableInformation ON tblCableCategory.cableCategoryID = tblCableInformation.cableCategory_PK) ON tblCableDestinationRack.cableDestinationRackID = tblCableInformation.cableDestination_PK) ON tblCableSourceRack.cableSourceRackID = tblCableInformation.cableSourceRack_PK

WHERE (((tblCableInformation.CableInfoID)=[Forms]![frmCableInformation]![CableInfoID]));

The reason I think that it is the WHERE Condition is because when I don't have the WHERE Condition on the query,

CurrentDb.Execute "apQryCableHistory"

works fine but it appends all the records from the main table to the archive table and I really just want to append a single record to the archive table when the cable information is edited or the cable is decommissioned.

 

[Pat Hartman]

You have to populate all the parameters. If there is more than 1, just add additional qs.Parameters! statements

Dim db As DAO.Database
Dim qd AS DAO.Querydef
    Set db = CurrentDb()
    Set qd = db.QueryDefs!apQryCableHistory
        qd.Parameters![enterCableID] = Me.CableID

    qd.Execute