I have three tables:
3. business_addresses (m2m table)
The PK for each record is currently created in business_names and addresses tables. Then the ids are stored in the many-to-many table business_addresses and foreign keys are also set up.
All tickety-boo until I want to delete a business.
In that scenario, The business records are removed as are those in the business_addresses table but the address record stays in the addresses table.
I know that is how it should work, given how I have set the thing up but, I think it is wrongly done.
Should I store the business_id in the business_addresses table, such that a PK is created for the address and then that PK for address is used in the addresses table?
if not, how else can FKs be used to cascade through the tables?