Constraining data based on related data in a different table. Should this be done on the database level or the application level?

Question

I am working on a database that contains our customer information as well as information about our vendors. Part of that includes matching our customers with our vendors, the account numbers those vendors provide to us for our customers and what we charge our customers to use those vendor's services.

Background

In the situation I'm working on now, I have a table that contains our customer's account number, the vendor ID and the account number assigned to our customer by that vendor.

I also have a table that contains all of our vendor's information and a related table that list's all of their services and a "base cost" for that service.

The Problem

The problem that I have is associating a customer with a vendor's service.

If I just create a table that has our customer's account number, a vendor's service ID and the cost of that service, then I could potentially run into issues where a service is assigned to a customer by a vendor that doesn't offer that service.

Here's a diagram of what I'm talking about (though crude):

Because there is no "check" between VendorServices and CustomerVendorServices, I could potentially insert a record where the VendorServiceID in the CustomerVendorServices table for a service that a vendor doesn't provide.

This is obviously a problem and I could prevent it from happening through the interface of the application that interacts with this database, but I would feel much more comfortable if there was a way for the database to "check" that the VendorServiceID being inserted into the CustomerVendorServices table matches a service that is offered by the vendor who is associated with the VendorAccountsID/VendorAccountNumber.

I'm sure this is beyond confusing and I'd honestly be surprised if anyone tried to understand it, but I thought I would give it a shot. :)

Any help and/or suggestions would be very much appreciated.

Answer 1

From my understanding of your question, I feel this might work.

1. Remove CustomerVendorServices table from your design.
2. Add ServiceID and CustomerCost in VendorAccounts table.
3. Remove the FK reference for VendorID in VendorAccounts table from Vendors table.
4. Add a FK reference for VendorID in VendorAccounts table from VendorServices table.
5. Also have a separate Services table identified by ServiceID having ServiceDescription and DefaultCost.
6. VendorServices will then have a composite primary key of VendorID and ServiceID.

Answer 2

ASSERTION

SQL-92 syntax defines a constraint called an ASSERTION. This is what you want.

Unfortunately, very few RDBMS have implemented ASSERTION. Oracle does not yet support ASSERTIONs

The ASSERTION you want to enforce would probably be:

NOT EXISTS (
select *
from CustomerVendorServices a
where (select b.VendorID from VendorAccounts b where b.ID=a.VendorAccountsID)
  <>  (select c.VendorID from VendorServices c where c.ServiceID=a.VendorServiceID)
)

Workaround

For the majority of RDBMS, you would have to implemented the checks manually.

That means, you will need to implement the checks within your Application or within your Table APIs (database package/procedure for doing DML against a single table).

Make sure you row-lock the appropriate rows of the parent table so they don't change between SELECT and INSERT.

Answer 3

A hack that I have used before (in MS SQL Server) is:

1. create a view that will return forbidden rows.
2. Add an unrelated table to the view that is guaranteed to have multiple rows.
3. Add a unique index on the view.

Thus, if a forbidden row is added, the view would end up trying to add multiple rows to the index; this is forbidden, so the original forbidden row cannot be added.

In your example as I understand it, a forbidden row is a row in CustomerVendorServices that matches VendorAccounts and matches VendorServices, but where VendorAccounts.VendorID does not match VendorServices.VendorID. Right? Those forbidden rows would be:

SELECT CVS.ID, CVS.VendorAccountsID, CVS.VendorServiceID, VS.VendorID, VA.VendorID
FROM CustomerVendorServices AS CVS
INNER JOIN VendorAccounts AS VA ON CVS.VendorAccountsID=VA.ID
INNER JOIN VendorServices AS VS ON CVS.VendorServiceID=VS.ID
WHERE VA.VendorID <> VS.VendorID

We need a completely unrelated table to supply extra rows. If you don't have such a table handy, it can be any subquery expression that returns more than one row. We create a view:

CREATE VIEW vw_forbidden_CVS AS
SELECT CVS.ID AS CustomerVendorServicesID,
       CVS.VendorAccountsID, CVS.VendorServiceID, 
       VS.VendorID, VA.VendorID
FROM CustomerVendorServices AS CVS
  INNER JOIN VendorAccounts AS VA ON CVS.VendorAccountsID=VA.ID
  INNER JOIN VendorServices AS VS ON CVS.VendorServiceID=VS.ID
,(SELECT 1 AS N UNION ALL SELECT 2 AS N) AS [2ROWS]
WHERE VA.VendorID <> VS.VendorID

and we create an index on this view:

CREATE UNIQUE INDEX vx_forbidden_CVS ON vw_forbidden_CVS (CustomerVendorServicesID)

And done.