Need help - using referential Integrity to cascade delete

[PSI]

In a nutshell I have multiple "things" each of which can have multiple
Address's. I want to set it up so deleting a "thing" deletes all of
its Address's. So at the moment I have:

Table Business
==========
BusinessID - autonumber, PK
Name - text

Table Employee
==========
EmployeeID -autonumber, PK
Name - text

Table Address
============
AddressID - autonumber, PK
Address - text
Type - int ( Bill=1, Ship=2, Home=3 etc )

Table BusinessAddress
==============
BusinessID - number, references BusinessID,PK
AddressID - number, references AddressID,PK

Table EmployeeAddress
==============
EmployeeID - number, references EmployeeID,PK
AddressID - number, references AddressID,PK

RFI and cascade delete are set on all relations.

When I delete a Business Record the corresponding BusinessAddress
records are deleted but the Address records aren't. ( Likewise for
Employee ).

Although I'm new to Access/DB design it seems this should be a
relatively common thing to do but I've been chasing my tail for days.
Any suggestions would be greatly appreciated.

Thanks
Frank

 

[PSI]

Rebecca,

I've been programming for 20+ years ( mostly engineering apps ) and
I'm starting to really feel dense because I can't get this set up!

Following your suggestion I now have the following ( very boiled down
for simplicity )

Entity Table
=========
EntityID - Autonumber,PK
CategoryType - number

Business Table
==========
BusinessID - Autonumber,PK : : references EntityID one-to-one
Contact - text

EmployeeTable
=========
EmployeeID - Autonumber,PK : references EntityID one-to-one
SocialSecurity - text

AddressTable
=========
AddressID - autonumber, PK
EntityID - number : references Entity.EntityID one-to-many
Address - text

I then start to add data manually in the Datasheet views and
inevitably run into a "You cannot add or change a record because a
related record is required in table xxxx". Conceptually I think I
understand this because the Entity record is expecting the same key in
both category tables ( Business and Employee ) which I don't think is
possible because they are both Automumber.

But I can't see my way clear to make it right!!
If you would please bear with me and nudge me a little further in the
right direction I would really appreciate it.

Thanks
Frank