Hummm, I'm going to have think some more, again.
I see logic in the answers from both of you. And I think that is because your 'correctness' could work for me, if only in some circumstances. I hope that doesn't come across as critical. not meant to.
Previously, I had an address table and then, in the businesses table, I used address_id as an FK.
This could have been fine, if the address would always relate to a specific business. But more than one business might use it and so, if the address for a business were to change, and I amended the address record, it busted the address for others that use its id as their fk address_id.
That problem was addressed in rudy's post.
However, I gave each address it own pk and then related each business to it, via a m2m table.
That works fine until I delete one business because the m2m table doesn't cascade the removal from the address table. And of course, somewhere along this road, someone will say yes, but removal is not safe because other businesses may be using that same address.
So I ponder some more and recognise wisdom in ScallioXTX's comment.
A business might have a physical (postal), address. And it may have a billing address. And several businesses may be part of the company so they have the same billing address but different postal addresses.
So, what if I have an address table with a ( business_id, address_type ) as the PK?
That means that several businesses in the one company that are at the same place, will each have their own address record. So, when moving a business to a new address, many address records may need to be updated. Is this not clunky??
So, instead, if I create an address with its own PK and assign a unique key (address_id, business_id, address type), then we still have the issue of removal of records if a business moves location.
so then I wonder about this...
a table for addresses, with a unique key for (parent_business_id, business_id, address_type)
trouble with that is that we have created a portfolio of addresses per company/business group name, but we still have the issue of amending addresses if one part of the business moves to a new location.
To explain the in concreto element...
A business has several sub businesses in the same premises. eg Jimmy soaps bar, jimmy soaps restaurant both in the same building. each currently uses the same postal address and both use the same billing address.
Then the restaurant moves to another place leaving the bar where it is. billing address remains consistent but postal address needs to change.
So whilst the m2m table would enable several businesses to 'share' the one address, we need to be able to
- just break the association of a business with an address (without deleting the address), in case another is using it,
- enable the removal or replacement of several address records, if the whole premises relocates - simply, without faffin around with numerous address records.
- remove the address record if it is not being used elsewhere.
point 3, is the only issue remaining because constraints won't obviously cascade to the m2m table.
is this a moon-on-a-stick, requirement??