I am stuck on some business logic and was hoping any of you database
normalization geeks (and I say that with the utmost respect) had an
opinion......

Here is my situation:

I have to create a db containing a list of businesses and their owners.
Their owners have to each have their own log in to the db records.

The trouble is this: Each business has 2 addresses - the physical and the
mailing.
These two addresses will ALWAYS belong to the primary owner as well.
Also each secondary owner MAY have two mailing addresses but many will have
only one.
The reason the 2nd owner may have a second address is for seasonal purposes.
i.e. Sep-Mar is Florida, the rest of the time in NH.

So in the end each business will have no less than two addresses and no more
than four,
and also no less than one owner and no more than two.

I am having trouble normalizing the structure - or even deciding if I should
in this case b/c it is so ridiculous.

Any thoughts?

thanks all,
Chris

Not so ridiculous, actually rather common.

I would normalize and it should not be that difficult.

BUSINESS table
fields that describe a single business such as name, etc.

PERSON or maybe OWNER table
fields that describe a single person such as name, etc.

ADDRESS table
fields to describe an address such as street, town, zip, etc.

BUSINESS ADDRESS table
a join table that links the key of one business to the key of one
address per record. Multiple records for multiple address

PERSON ADDRESS table
a joint table that links the key of a person to a key of one address
per record. Extra fields could describe when a given address applies to
a person of this is desirable.

With this pattern it would be very easy to have as many or as few
address per entity as required. Because one thing one can guarantee
about data; "sooner or later there will be an exception." And it is
always best to plan for this as much as possible.

You may also want fields that allow you to do do 'soft' deletes by
describing records as active and inactive. That makes it easy to keep
historical data when addresses and names change that is not possible if
the older data is destroyed when this happens.

If you didn't normalize it, how would you structure it?

So far we know that a business can have many owners, so that's a one to many
relationship.

I would certainly acknowlege the possibility that an owner could have more
than one business, so let's change that to a many to many. The fields would be
business_id, owner_id, and primary_true_false.

The physical address seems to belong to the business, but could there be more
than one? If you can't find out, assume that there could. That's another one
to many, possibly having a field to indicate the primary address.

Owner addresses are one to many and appear to be date dependent. Looks like
you are going to need a table resembling

address_id, date
to indicate the current mailing address for that owner.

You have of course acknowleged the possibility that an owner may move, right?