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.

So Ian - you are saying that I need a table of addresses and that each record
would belong to either a peron or a business. Is that correct?
If so - I wonder doesn't that duplicate every business address at least once?
Since every business will have a primary address and it WILL be the exact same
as the primary owner at all times..... hmm

So what I've done betwixt posting and response is this:

created a business table containing basic business info:
bus_ID, bus_Name, bus_Active y/n, bus_Current, bus_Hours, bus_URL, etc.

then a users table containing:
user_ID, user_BusID (relating to table above of cuorse),
user_PhysicalAddressFields( addre,city,etc.),
user_MailingAdddressFields ( addre, city, etc.),
user_Phone, user_Email,
user_MailFromDate, user_MailToDate,
user_OwnerType, etc.

the user_OwnerType would be primary or secondary. If we had more later then
terciary etc.

But If I went with your tactic Ian I might have something like this...

tbl_Business:
bus_ID, bus_Name, etc.

tbl_Owners (same as Users table above)
owner_ID, owner_BusID, owner_Name, owner_Type (primary, secondary,etc), etc.

tbl_Mail
mail_ID, Mail_Address, Mail_City, mail_StartDate, mail_EndDate, etc.

tblOwnerMail
ownermail_ID, ownermail_OwnerID, ownermail_MailID, ownermail_Address, etc

tbl_BusMail
busmail_ID, busmail_BusID, busmail_MailID, busmail_Address, etc.

And then this would allow the owner and the business to share one mail record
(mail_ID) if needed.

Did I get that right? If so - I have mucho to think about.

So Dan - I am a little more confused by your response however. It seems to go
along with Ian's to a point.

As for an owner moving - we just change his address At that point are you
suggesting that the primary owner's mailing address will then be different then
the business's primary mailing address? If that is the case then I think that
Ian's solution may deal with that.

echowebs wrote:
> So Ian - you are saying that I need a table of addresses and that each record
> would belong to either a peron or a business. Is that correct?
> If so - I wonder doesn't that duplicate every business address at least once?
> Since every business will have a primary address and it WILL be the exact same
> as the primary owner at all times..... hmm

No, there is no reason that the business join and the person join can
not point to the exact same address record. This actually illuminates
duplicate records that would occur in just about any other scheme.


bus1, 'My Place of Business'...
per1, 'Mary Sue Von Der Longname', ...
add1, '123', 'Main Street', 'My Town', ...

Business Join
bus1, add1

Person Join
per1, add1, 'year around'

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?

So Ian - you are saying that I need a table of addresses and that each record
would belong to either a peron or a business. Is that correct?
If so - I wonder doesn't that duplicate every business address at least once?
Since every business will have a primary address and it WILL be the exact same
as the primary owner at all times..... hmm

So what I've done betwixt posting and response is this:

created a business table containing basic business info:
bus_ID, bus_Name, bus_Active y/n, bus_Current, bus_Hours, bus_URL, etc.

then a users table containing:
user_ID, user_BusID (relating to table above of cuorse),
user_PhysicalAddressFields( addre,city,etc.),
user_MailingAdddressFields ( addre, city, etc.),
user_Phone, user_Email,
user_MailFromDate, user_MailToDate,
user_OwnerType, etc.

the user_OwnerType would be primary or secondary. If we had more later then
terciary etc.

But If I went with your tactic Ian I might have something like this...

tbl_Business:
bus_ID, bus_Name, etc.

tbl_Owners (same as Users table above)
owner_ID, owner_BusID, owner_Name, owner_Type (primary, secondary,etc), etc.

tbl_Mail
mail_ID, Mail_Address, Mail_City, mail_StartDate, mail_EndDate, etc.

tblOwnerMail
ownermail_ID, ownermail_OwnerID, ownermail_MailID, ownermail_Address, etc

tbl_BusMail
busmail_ID, busmail_BusID, busmail_MailID, busmail_Address, etc.

And then this would allow the owner and the business to share one mail record
(mail_ID) if needed.

Did I get that right? If so - I have mucho to think about.

So Dan - I am a little more confused by your response however. It seems to go
along with Ian's to a point.

As for an owner moving - we just change his address At that point are you
suggesting that the primary owner's mailing address will then be different then
the business's primary mailing address? If that is the case then I think that
Ian's solution may deal with that.

echowebs wrote:
> So Ian - you are saying that I need a table of addresses and that each record
> would belong to either a peron or a business. Is that correct?
> If so - I wonder doesn't that duplicate every business address at least once?
> Since every business will have a primary address and it WILL be the exact same
> as the primary owner at all times..... hmm

No, there is no reason that the business join and the person join can
not point to the exact same address record. This actually illuminates
duplicate records that would occur in just about any other scheme.


bus1, 'My Place of Business'...
per1, 'Mary Sue Von Der Longname', ...
add1, '123', 'Main Street', 'My Town', ...

Business Join
bus1, add1

Person Join
per1, add1, 'year around'

[q]Originally posted by: echowebs
But If I went with your tactic Ian I might have something like this...

tbl_Business:
bus_ID, bus_Name, etc.

tbl_Owners (same as Users table above)
owner_ID, owner_BusID, owner_Name, owner_Type (primary, secondary,etc), etc.

tbl_Mail
mail_ID, mail_Type(phys vs. mail), Mail_Address, Mail_City, mail_StartDate,
mail_EndDate, etc.

tblOwnerMail
ownermail_ID, ownermail_OwnerID, ownermail_MailID, ownermail_Address, etc

tbl_BusMail
busmail_ID, busmail_BusID, busmail_MailID, busmail_Address, etc.

And then this would allow the owner and the business to share one mail record
(mail_ID) if needed.

Did I get that right? If so - I have mucho to think about.[/q]
Not exactly. For this,
tbl_Owners (same as Users table above)
owner_ID, owner_BusID, owner_Name, owner_Type (primary, secondary,etc), etc.

Unless you are absolutely sure that an owner can have more than one business
for as long as your database is being used, all you want in the owner's table
are things that have nothing to do with any business, such as name. Use a many
to many table to marry up business and owner info.

For your address stuff, take the start and start stuff out of the tbl_mail
table. All you want is an id and the street, city, etc. Use separate tables
to identify physical vs mail, and effective dates.

Many to many tables are generally more effective with multi-field primary
keys. For example, if you had an owners table and a mail table, with owner_id
and mail_id as the primary keys, and there were no date considerations, then
owner_id, mail_id would be a good primary key for an owner_mail table. In your
case you would need something else to specify primary and effective dates.
What you don't want is an owner_mail_id field. That enables you to generate
duplicate records.