Enforce business rules though referential integrity

[winshent]

I am building a new project which has some core tables..

The key tables are:

Countries
CountryID
CountryName

Businesses
BusinessID
BusinessName
CountryID

Clients
ClientID
ClientName
BusinessID
CountryID

I want to enforce the rule that a Business can only have clients within the same country.

Can I enforce this through referential integrity in Access ? Do I need to redesign my tables ?

 

[llkhoutx]

Yes you can.

 

[MarkK]

Well, you could enforce it by not attaching country data to Client, so that table becomes . . .

Clients
ClientID
ClientName
BusinessID

. . . and if you want to make reference to the country of the Client, you connect to this information via the Business table.

Use SQL like this to return the CountryID of a client . . .

SELECT b.CountryID 
FROM Business As b INNER JOIN Client As c ON b.BusinsessID = c.BusinessID
WHERE c.ClientID = ?

 

[winshent]

Use SQL like this to return the CountryID of a client . . .

SELECT b.CountryID 
FROM Business As b INNER JOIN Client As c ON b.BusinsessID = c.BusinessID
WHERE c.ClientID = ?

Okay.. I see what you are getting at.. but it doesn't help me.. I'll explain..

I have further data that is attached to a client. This data is taken from a lookup table, and is country specific.. The principle is the same as my original post. I want to enforce business rules between tables that are indirectly attached.

For example, ProductTypes is a lookup.. and the client data is stored in ClientProducts with further attributes..

ProductTypes
ProductTypeID
ProductName
CountryID

ClientProducts
ClientID
ProductTypeID
Attr1

I want to limit only the ProductTypes only to those that are available in the country of the Business/ Client.

I was wondering if this can be done through database design..