advice on using default sets of information please

From: Neil <neil_b_at_yahoo.co.uk>
Date: 8 Nov 2004 09:38:04 -0800
Message-ID: <3d4d06e7.0411080938.4a67796c_at_posting.google.com>

I have clients and services and most clients get a specialised selection of services. The remaining clients have a pre-defined default set of services. This default set may vary over time and I will want the clients receiving it to always get the up-to-date set. How best should I model this? I am using a relational database.

Here is the simple case where every client has an explicitly defined set of services:

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));

This won't do, as I can't tell either which clients have the default set or what the default services are (unless the application supplies the ClientID for the fake client).

I have thought of a few different ways to handle this - each with problems - , and I would appreciate some opinions, or pointers, from this newsgroup.

TIA Received on Mon Nov 08 2004 - 18:38:04 CET