Cascade Deletes

[Bee]

Hi,

I want to use Cascade Delete on a main table that has relationships with a few tables. The type of these relationships are weak and the delete will work perfectly I think. But, I am concerned about how I can only delete the FK from one of the tables because it contains a non-prime FK.

For example: Customer buys House. So Customer PK is in House as FK.
If I delete customer, I don't want house deleted. Is there anyway that is native to access in how I can only delete the FK from tblHouse without deleting the house record.

Any help will be very much appreciated.
B

 

[CraigDolphin]

cascade delete will only delete the many side of a one to many relationship.

In your case, I think you need to rethink your db design if your example is from your db.

In your example, you can only have one buyer associated with many houses. This is not what you would want. What if more than one buyer jointly purchase a house?

Think ...
TableHouse
HouseID (Pk)

TableBuyer
BuyerID (PK)

TableHouseBuyers
HouseBuyerID (PK)
HouseID (FK)
BuyerID (FK)
BuyDate (maybe...)

If you delete a record from TableHouseBuyer this will not cascade delete the buyer or the house. Deleting a buyer from TableBuyer will delete any associated records in TableHouseBuyer but not in TableHouse. Similarly, Deleting a record in TableHouse will delete associated records in TableHouseBuyer, but not in TableBuyer.

 

[gemma-the-husky]

ignoring the cascading deletes

why would you want to delete the customer. Assuming you are in an esate agency of some sort, then if you (only) delete the cutomer and not the house you leave a "floating" house sale "pointing" to a non-existient customer . If the customer withdrew from the sale then perhaps you want to set the FK in the house table to zero, before you delete the customer.

IPersonally i think cascading deletes are dangerous. Its often better to set a flag on a record to eg your customer to "dead" rather than delete it. But if you do need to delete subsidiary records, I would generally specifically delete them myself rather than let Access manage it "behind the scenes".

 

[Bee]

ignoring the cascading deletes

why would you want to delete the customer. Assuming you are in an esate agency of some sort, then if you (only) delete the cutomer and not the house you leave a "floating" house sale "pointing" to a non-existient customer . If the customer withdrew from the sale then perhaps you want to set the FK in the house table to zero, before you delete the customer.

IPersonally i think cascading deletes are dangerous. Its often better to set a flag on a record to eg your customer to "dead" rather than delete it. But if you do need to delete subsidiary records, I would generally specifically delete them myself rather than let Access manage it "behind the scenes".

I want to do the cascade delete to delete Customer information and House info that's not needed any more. Sometimes, the user will only need to get rid of the customer, but not the house. Using flags is good as you said; however, won't that make the DB large over time?

Cheers,
B

 

[CraigDolphin]

A yes/no 'BuyerAlive' field in your buyer table is unlikely to make any serious diffference to the size of your db and, in this situation, would make much more sense than deleting the person (and any associated house sales that the person was involved in).

You can always limit your list of buyers to those that are not deceased.

 

[Bee]

A yes/no 'BuyerAlive' field in your buyer table is unlikely to make any serious diffference to the size of your db and, in this situation, would make much more sense than deleting the person (and any associated house sales that the person was involved in).

You can always limit your list of buyers to those that are not deceased.

No I meant the fields that make up the core information such as names, address..etc are the once that can make the DB full. If they are not needed after a certain time, why should keep them. For example, the company may not need info that is more than 1 year old anymore. Hence, deleting old records will always keep the DB size controlled and (In addition to other maintenance stuff) this will also keep the DB perfromance sound.

 

[CraigDolphin]

If they are not needed after a certain time, why should keep them.

If file size becomes an issue (very unlikely IMO unless you're talking hundreds of thousands of records+ in multiple tables in a properly normalized db) then maybe you'd want to purge (or move to an archive db) some of the older records. If not, why delete them? Data is valuable. Throwing it away after you've done all the hard work of entering it (unless you have a compelling reason) seems wasteful to me. Remember, it's easy to get rid of data at any time...it's much harder to reconstruct data after time has passed.

company may not need info that is more than 1 year old anymore

That might be true now, but will it be true always? Consider, what if a new 'boss' takes over in 5 years and decides they want to look at market trends over time? (sales per quarter, sales per year, median price per sale by quarter over time by suburb etc).
Or what if your boss cottons to the idea that they can sell historical ->current information about the local housing market to other companies who don't have a clever db person like yourself?

If you've deleted the older sales records, you can't oblige. If you still have them, then there's no problem.

A person may no longer be a useful contact after they are dead, but their purchases while alive are still interesting data for someone researching market trends.

Just remember to compact and repair (and backup) the db regularly to avoid bloat. And don't store photos in access either. Keep an eye on file size after a compact/repair and if you ever start getting towards 1GB then start thinking about archiving old data.

At least, that's my opinion. It's your database, and my opinion is worth only as much as you paid for it

 

[Bee]

I appreciate this, it's good to know.
Thank you,
B

 

[gemma-the-husky]

especially as the house buyer cycle runs in about 7 - 10 years - in fact mailshotting your buyers/clients after about 6-7 years might drum up some more business