Hi Jamie

Yes, it was a misstatement - I meant to say "cannot both be NON-Null" :-)

Your solution involving duplicating the sub-type in the sub-tables and using
a two-field relationship is simple, elegant and brilliant. You say this is
"the standard approach" but I can't believe that I have never come across it
before, and it is so simple that I'm ashamed I never thought of it myself!

One question though - is there any reason not to put the "common" fields in
the superclass table? In other words, why not combine the Contacts and
LegalPersons tables?
--
Thanks!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Jamie Collins" wrote in message
ups.com...

Graham Mandeno wrote:
In a Jet (Access) database, there is no way to enforce this sort of
entity
subclassing at the engine level. The only way to do that would be to
have
two FK fields in tblContacts - one for IndivID and one for OrgID, and
have a
table-level constraint (validation rule) to specify that they cannot both
be
Null.

That's got to be a misstatement!

The standard approach to implementing such subclass is to have a
superclass for both individuals and organizations; this can certainly
be implemented in Jet using referential integrity. If there are only
two types, each of which needs a base table in the schema, a
ContactTypes table is overkill:

[I'm avoiding PRIMARY KEY in favour of NOT NULL UNIQUE because
clustering on disk is OT; syntax requires ANSI query mode in the Access
UI or an OLE DB (e.g. ADO) connection.]

CREATE TABLE LegalPersons (
legal_person_ID INTEGER NOT NULL UNIQUE,
legal_person_type VARCHAR(5) NOT NULL,
CHECK (legal_person_type IN ('Indiv', 'org')),
UNIQUE (legal_person_type, legal_person_ID)
);

CREATE TABLE Individuals (
legal_person_ID INTEGER NOT NULL UNIQUE,
legal_person_type VARCHAR(5) NOT NULL,
CHECK (legal_person_type = 'Indiv'),
UNIQUE (legal_person_type, legal_person_ID),
FOREIGN KEY (legal_person_type, legal_person_ID)
REFERENCES LegalPersons (legal_person_type, legal_person_ID),
last_name VARCHAR(35) NOT NULL...
);

CREATE TABLE Organizations (
legal_person_ID INTEGER NOT NULL UNIQUE,
legal_person_type VARCHAR(5) NOT NULL,
CHECK (legal_person_type = 'Org'),
UNIQUE (legal_person_type, legal_person_ID),
FOREIGN KEY (legal_person_type, legal_person_ID)
REFERENCES LegalPersons (legal_person_type, legal_person_ID),
trading_name NVARCHAR(255) NOT NULL...
);

CREATE TABLE Contacts (
legal_person_ID INTEGER NOT NULL,
legal_person_type VARCHAR(5) NOT NULL,
FOREIGN KEY (legal_person_type, legal_person_ID)
REFERENCES LegalPersons (legal_person_type, legal_person_ID),
address_line_1 VARCHAR(30) NOT NULL...
needs a key
);


OK, I know what you are thinking: how to prevent a contact being
created for a row in LegalPersons that does not exist in either
Individuals or Organizations?

Something like this:

CHECK (1 = (SELECT IIF(Contacts.legal_person_type = 'Org', 1, COUNT(*))
FROM Individuals
WHERE Individuals.legal_person_ID = Contacts.legal_person_ID)
),
CHECK (1 = (SELECT IIF(Contacts.legal_person_type = 'Indiv', 1,
COUNT(*))
FROM Organizations
WHERE Organizations.legal_person_ID = Contacts.legal_person_ID)
),
...

Even you implied structure (i.e. omitting the LegalPerson table), you
*can* implement the foreign keys in Jet: using the same CHECKs as
above:

CREATE TABLE Individuals (
individual_ID INTEGER NOT NULL UNIQUE,
last_name VARCHAR(35) NOT NULL...
);

CREATE TABLE Organizations (
organization_ID INTEGER NOT NULL UNIQUE,
trading_name NVARCHAR(255) NOT NULL...
);

CREATE TABLE Contacts (
legal_person_ID INTEGER NOT NULL,
legal_person_type VARCHAR(5) NOT NULL,
CHECK (legal_person_type IN ('Indiv', 'org')),
CHECK (1 = (SELECT IIF(Contacts.legal_person_type = 'Org', 1, COUNT(*))
FROM Individuals
WHERE Individuals.individual_ID = Contacts.legal_person_ID)
),
CHECK (1 = (SELECT IIF(Contacts.legal_person_type = 'Indiv', 1,
COUNT(*))
FROM Organizations
WHERE Organizations.organization_ID = Contacts.legal_person_ID)
),
address_line_1 VARCHAR(30) NOT NULL...
needs a key
);

I would not recommend 'mixing' identifiers in this way. However, I
trust the above is sufficient for you to realize that it is possible to
enforce such constraints at the engine level in Jet.

Jamie.

--