Skip to main content
A
Anonymous
August 26, 2008
Question

Normalization - should I bother

  • August 26, 2008
  • 5 replies
  • 364 views
Sorry if this is in the wrong topic....

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
    This topic has been closed for replies.

    5 replies

    N
    Newsgroup_User
    Inspiring
    August 26, 2008
    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'



    A
    Anonymous
    August 26, 2008
    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.
    A
    Anonymous
    August 26, 2008
    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_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.
    D
    Dan_Bracuk
    Inspiring
    August 26, 2008
    quote:
    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.

    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.
    D
    Dan_Bracuk
    Inspiring
    August 26, 2008
    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?
    N
    Newsgroup_User
    Inspiring
    August 26, 2008
    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.



    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices