table and FK structure question

[IBazz]

Hi,

I have three tables:

1. business_names
2. addresses
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?

bazz

[ScallioXTX]

Think about whether the m2m makes sense. If 2 businesses have the same address, is that semantically relevant? If one of those businesses moves, does the other one move as well?

[IBazz]

Thanks ScallioXTX

I have busted my head in the past trying to work this out and thought the m2m table was the correct way.

I accept that similar addresses for several businesses is irrelevant for the reason you eluded to.

But then, I have some clients with several businesses at the same address so if the address of one moves, they MAY all change but not necessarily so.

That, and the fact that they have more than one address per business such as billing and postal.

If my addresses table were to have (business_id, address type) as the PK, then if one business moved from there, changing the records doesn't strike me as being difficult.

But if the several businesses owned by one company - operating currently from the same address, were later to move, I have multiple addresses to change.

Trying to keep it simple but as usual, I think I am making it more complicated than it needs to be.

Still pondering and have willing ears.

bazz

[ScallioXTX]

If my addresses table were to have (business_id, address type) as the PK, then if one business moved from there, changing the records doesn't strike me as being difficult.

I would prefer to have a bit more work on updating addresses when a bunch of them move, than having an incorrect new address of some companies that didn't move (how would you even find out that happened, and could you easily revert it?).

Maybe you should build that a company can have sub-companies and they all have the addresses of the parent company? That makes logical sense. Well, to me anyway.

[r937]

the addresses table has made several previous appearances in other threads, and i can only repeat my observation ...

if you care about addresses, regardless of whether there's a business at an address or not, then addresses deserves their own identity and their own table

otherwise, address is an attribute of a business and should not get its own PK

the comparison i always like to make is with an employees table -- there might be multiple employees with the first name "john" but that doesn't mean you have to declare a firstnames table and link the employees table to it with a many-to-many relationship...

[IBazz]

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

1. just break the association of a business with an address (without deleting the address), in case another is using it,
2. enable the removal or replacement of several address records, if the whole premises relocates - simply, without faffin around with numerous address records.
3. 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??

bazz

[r937]

if a business can have multiple addresses, then yes, technically you have a separate addresses table

but here's the thing, it's a dependent entity, which means that its PK contains the business FK

i believe this is what you meant by "( business_id, address_type ) as the PK" and i concur with this design

it's the many-to-many structure i object to, where the addresses have their own independent PK

sometimes, in the design stage, it helps to look ahead to the programming phase, and try to imagine the user interaction and necessary forms to complete a task

try to anticipate what the sql is going to be for tasks like address updates, business deletion, and so on

i believe you nailed the problem with your point 3, bazz

i just don't like the many-to-many structure

[IBazz]

Thanks rudy,

I'm not overly fussed now on the m2m relationship but, I am stuck with it until I find an alternative.

I don't like the thought of numerous redundant address records.

Time to sleep on it and come back to it again tomorrow.

bazz

[r937]

I don't like the thought of numerous redundant address records.

in practical terms (e.g. the necessary sql to manage them), what is the difference between saying that and saying "i don't like the thought of numerous redundant first names in my employees table"?