Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2004
    Posts
    10

    Unanswered: Company - Address

    I need some help devising my tables.

    I have a company table and a address table. I have created a linked table with two foreign keys from the company table and the address table respectively.

    This set up allows me to apply more than one address per company which is fine. My problem is that I need a way to prevent a company from entering exactly the same address twice.

    In the address table i have the following fields:

    AddressKey - PK
    CompanyKey - FK
    Address 1 - 3
    Town
    County
    Country
    PostCode

    In the company table I have the following fields.

    CompanyKey - PK
    FirstName
    Secondname

    And finally in the link table I have the following:

    CompanyKey - FK
    AddressKey - FK.

    Now, if i entered the following into the address table Assuming that company id of 1 was already entered into the company table.

    AddressKey - 1, CompanyKey = 1, Address1 = 11 Address2= Taylor

    I need a way of preventing this from happening.

    AddressKey - 2, CompanyKey = 1, Address1 = 11 Address2= Taylor

    As can be seen the PK - FK values are unique and are correct for referential integrity, but the actual address is the same.

    Any help we be mostly appreciated.

    Cheers

    Wayne
    you've got to go back before you can go forward.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you do not need the linking table at all, because you apparently want to link each address to its company using the CompanyKey as a FK in the address table

    the only time you need a linking table is when multiple companies share the same address

    and even if this does occur, it probably does not occur all that frequently (compared to the bulk of your data)

    therefore if two companies have the same address, put that address into the table twice

    now, as to your question...

    to prevent the same company from entering the same address twice, declare a unique constraint on ( CompanyKey, Address 1 - 3, Town, County, Country, PostCode )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Posts
    10
    r937.

    Thanks for the reply.

    ok I understand why you would not use the linked table, but In my case the address table is in effect the site address of a particular company, and companies can have multiple site addresses.

    If i don't use a linked table then this would not be possible.

    I tried using an instead of trigger - would this not be a good idea in this case then?

    Cheers

    Wayne
    you've got to go back before you can go forward.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let's make sure we are talking about the same linked table

    you can support the one-to-many model of a company having multiple addresses just with Company and Address table

    the Address table CompanyKey is the FK that says which company this is an address for

    the 3rd table, the link table, is not necessary

    unless, like i said earlier, you wnt two different companies to "share" an address!

    as far as preventing a compnay from entering the same address twice, no, you would not use a trigger for that, you would use a unique constraint for that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2004
    Posts
    10
    r937.

    Thanks for clearing that up for me. I will give it a stab once i've finished browsing ebay. Ok to give you a shout if I have any problems?

    Wayne
    you've got to go back before you can go forward.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you were thinking of contacting me personally through a private message or email, i would prefer that you post a followup in this thread

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2004
    Posts
    10
    sorry that's what I meant!
    you've got to go back before you can go forward.

  8. #8
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    here would be a way to solve your issue by using a composite key(or Unique index)and an intersecting entity
    your business rule dictates that one customer can have many addresses and that no duplicate addresses can be used

    you could create a customer table

    customerid PK
    companyname
    addressid

    then create a address table

    Addressid PK
    StreetAddress
    city
    state etc..

    now the issue exists that you cannot get 2 addresses out of this erd but you can by modifying the erd with an intersecting entity..

    create a customeraddress table

    Customerid PK
    addressid PK
    (any dependent data on a customer at a particular address goes in this table)
    the composite key on the two columns generates uniqueness for each of them. (no one customer can have a duplicate address)

    The join path would look like this
    Customer <-------------> CustomerAddress <-------------->Address

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Ruprect, that's beautiful, but that's the many-to-many relationship

    i have my doubts about whether that's required

    i think only a one-to-many is required

    and in any case, if you go right back to the original question, the situation was

    AddressKey - 1, Address1 = 11 Address2= Taylor
    AddressKey - 2, Address1 = 11 Address2= Taylor

    and thus possible to give the "same" address to a company twice, because the "same" address is actually in the table twice! (this is a problem of surrogate keys, not one-to-many versus many-to-many)

    your 3-table many-to-many does not prevent that duplication either

    here's what i recommend:
    Code:
    create table Companies
    ( CompanyKey 
    , FirstName
    , Secondname
    , primary key (CompanyKey)
    )
    create table Addresses
    ( AddressKey 
    , CompanyKey 
    , Address1
    , Address2
    , Address3
    , Town
    , County
    , Country
    , PostCode
    , primary key (AddressKey)
    , foreign key (CompanyKey) references Companies (CompanyKey)
    , unique (Address1, Address2, Address3, Town, County, Country, PostCode)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2004
    Posts
    10
    Sunday Morning.

    Just read your replies guys. Ruprect as r937 states, this was my original solution and does not prevent a duplicate address from existing, ok it does allow me to create a unique constrant based on the companykey and addresskey of the respective tables but this was not what I was after. - Thanks for the input though.

    r937 - going to that last reply a stab.

    Thanks for all your helps guys.

    Wayne
    you've got to go back before you can go forward.

  11. #11
    Join Date
    Apr 2004
    Posts
    10
    Thats worked fine.

    Next question is how do I take that sever error message that is created (Server msg 2627) and produce a user friendly error message stating that they have violated key constraints.

    Would I design a trigger for this?
    you've got to go back before you can go forward.

  12. #12
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    wait
    now hold on justa cotton picken minute rabbit! ( channeling yosemite sam)

    you solution limits the address process the actual # of cols (in this case 3) what if someone has 4 addresses are you going to alter the table?

    my solution which is typically a many to many solution also eliminates the dupes in the customeraddresstable by creating a composite unique constraint or index on the customerid and addressid columns


    here are the customers and address tables with some data
    customer c Address a
    c1 a
    c2 b
    c3 c
    c4 d
    c5 e

    here is the customeraddress table

    c1 a
    c1 b
    c1 c
    c1 d
    c1 e

    if i try to add customer c1 with the address a again i will violate the constraint. no dupes and there is no limit to the possible addresses that one customer can have.

    i wouldnt like to use it because of the extra joins so the previous solution might have a perf advantage. but my solutin does work.
    Last edited by Ruprect; 04-18-04 at 13:31.

  13. #13
    Join Date
    Apr 2004
    Posts
    10
    in the end guys i cam up with this design
    Attached Files Attached Files
    you've got to go back before you can go forward.

  14. #14
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    cool! good for you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •