I am designing a solution where I have Associates in an Associates Table. Each associate could have a number of business connections such as being a Customer, a Supplier, a Member or an Employee (or any other category). I have these categories in a Business Category table.
I now have a link table called Business Associations which contains (amongst other fields) the two foreign keys to be able to match up the Associate with one or more categories.
My thoughts now turn to the addresses. I was considering whether the addresses should be on the Associates table or on the Business Associations table. However as it is possible to have multiple addresses I am tending towards putting all addresses into an addresses table. The relevant business association (e.g. customer or supplier) would then reference to the required address from this table.
I want to be able to restrict addresses to particular associates so I have included a foreign key on the addresses table (_fkAssocID) to link to the associate table. Thus once an address is set up for an associate it can be used by that associate for any other business category (e.g. supplier, employee...)
My question therefore is whether this is a sensible way to approach this or is there a better or preferred way?
Thank you for your guidance and help.