Prevent data duplication between two fields

[infotech2]

I have a database for entering service orders. I need to make sure that a Service Company isn’t assigned the same "Place" more than once. Places can have the same name as long as they are in different countries, or are assigned to different companies but a service company should never have a duplicate Place names.

What is the best way to accomplish this based on the table structure below?

<b>tblPlaces</b>
PlaceID (primary key)
SVCID (foreign key
PlaceName
PlaceCountry

<b>tblServiceCompany</b>
SVCID (primary key)
SVCName

 

[JoeAtWork]

I think you need a middle join table.

So, I would have:

[tt]
tblPlaces
PlaceID (primary key)
PlaceName
PlaceCountry

tblServiceCompanyPlaces
SVCID (foreign key) \____(primary key)
PlaceID (foreign key) /

tblServiceCompany
SVCID (primary key)
SVCName
[/tt]

The primary key of tblServiceCompanyPlaces (SVCID + PlaceID) ensures that a service company is not assigned to a place twice.

 

[PHV]

blPlaces
PlaceID (primary key)
SVCID (foreign key)
PlaceName
PlaceCountry

unique index (PlaceName, SVCID)
unique index (PlaceName, PlaceCountry)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886