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

Wayneyou 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 )|||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|||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|||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|||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

:cool:|||sorry that's what I meant!|||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|||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:
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)|||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|||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?|||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.|||in the end guys i cam up with this design|||cool! good for you