Re: advice on using default sets of information please

Neil wrote:

>
> CREATE TABLE Client(
> ClientID NUMERIC NOT NULL PRIMARY KEY,
> Name CHAR(90) NOT NULL);
>
> CREATE TABLE Service(
> Code VARCHAR(2) NOT NULL PRIMARY KEY,
> Name VARCHAR(40) NOT NULL);
>
> CREATE TABLE ClientService(
> ClientID NUMERIC NOT NULL,
> Code VARCHAR(2) NOT NULL,
> FOREIGN KEY (ClientID ) REFERENCES Client (ClientID ),
> FOREIGN KEY (Code) REFERENCES Service (Code),
> PRIMARY KEY (ClientID ,Code));
>

Neil, you are missing a few definitions, but you are basically on the right track.

First question, is the default template applied when a client signs on, and then you go forward from there, or do changes to the default propagate to existing clients? This is not clear from your post.

The following approach should basically work, with some tweaking based on the answer to the above.

Define a table similar to your ClientService above called DefaultClientService (or whatever) that contains the default definition.

Every client is assumed to have the default entries, and now some clients will have additional entries, and some will have entries removed.

Add a column "Mask" to your table ClientService, which can have the values "I" or "D". An "I" entry is an insert, it adds a row to the default. A "D" entry deletes or masks a row from the default.

Then make a view that is a UNION of the DefaultClientService with ClientService, having the more specific entries in ClientService "mask" the entries in the default, something like this maybe:

SELECT 'F' as mask,code FROM DefaultClientService   WHERE code NOT IN

      SELECT code FROM ClientService 
        WHERE mask = 'D'
          AND ClientID = 'X'

This is off the top of my head, but should get the idea across. The 'F' as mask from DefaultClientService means De"F"ault.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me