Re: advice on using default sets of information please

Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net> wrote in message news:<mor462-l21.ln1_at_pluto.downsfam.net>...
> 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));
> >
> 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.

Changes to the default propagate.  

> 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'
> UNION ALL
> SELECT mask,code FROM ClientService
> WHERE mask = 'I'
> 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.

Certainly this is an approach I hadn't considered. Thanks a lot! I'll chew it over and post again in a day or so. - Neil Received on Tue Nov 09 2004 - 10:48:24 CET