Mapping n-nary Entity Relational diagram to Tables

[Eric]

Hi everyone,

I'm a novice to database design and would appreciate if
any one can solve this.

I will like to ask if I have a superclass called Complaint
and several subclass like Quality, Handling and so on, how
do I map them into a table when the subclasses had no
attribute and they need to be disjoint?

Can I use flags for the different subclass? Or do I create
a unique Id for each of the subclass like Quality_ID?

Thanks a lot!
Eric

 

[Tim Ferguson]

I don't really understand what you are asking here, but I'll have a go at
picking out some of the things I think you might be getting at.

I will like to ask if I have a superclass called Complaint
and several subclass like Quality, Handling and so on, how
do I map them into a table when the subclasses had no
attribute and they need to be disjoint?

You cannot map the subclasses into one table, because of the simple fact
that they in separate tables because they have different attributes. The
attributes that are shared should be in the superclass (Complaints) table.

Can I use flags for the different subclass? Or do I create
a unique Id for each of the subclass like Quality_ID?

The unique ID for every complaint is the primary key for the Complaints
table, and this is used as a 1:1 FK and PK for the subclass tables.

Unfortunately you cannot persuade Jet to guarantee that each complaint only
has one subclass record. This may or may not fit your business model -- can
one complaint refer to quality problems and handling errors? If you
absolutely have to have one subclass per complaint, then you'll need to
move up to a real RDBMS like SQL Server (or MSDE, which is free) etc and
use database triggers. In the meantime, however, you can use a bit of code
behind a form to force the same thing, and take steps to prevent users
accessing the data any other way.

Hope that helps


Tim F

 

[Mike Sherrill]

Unfortunately you cannot persuade Jet to guarantee that each complaint only
has one subclass record.

With the right structure, Jet can guarantee that, if a row exists in a
subtype table, there won't be corresponding rows in any other subtype
table.

So if I have a complaint (supertype) about quality (subtype), Jet can
guarantee the subtype data--*if* it exists--will be found only in the
subtype table "Quality". But Jet can't guarantee that subtype data
will exist. The best support for this kind of stuff requires an
engine that lets you defer constraint checking until the end of a
transaction.

You have to store a code for the subtype in the supertype, but you'd
do that anyway. (In relational literature, the terms are "supertype"
and "subtype"; "superclass" and "subclass" are something else.)

The ugly part is the UNIQUE constraint on the PK and subtype column in
the first table. You have to have it in order to enforce a foreign
key constraint in the second table.

(air code)

CREATE TABLE Complaint (
Complaint_Num INTEGER,
Complaint_Type VARCHAR(8),
-- other columns
CONSTRAINT Complaint_PK
PRIMARY KEY (Complaint_Num),
CONSTRAINT Complaint_Unique
UNIQUE (Complaint_Num, Complaint_Type)
);

CREATE TABLE Quality (
Complaint_Num INTEGER,
Complaint_Type VARCHAR(8),
-- other columns
CONSTRAINT Quality_PK
PRIMARY KEY (Complaint_Num),
-- This foreign key constraint requires a unique index
-- on these two columns in the referenced table.
CONSTRAINT Quality_only
FOREIGN KEY (Complaint_Num, Complaint_Type)
REFERENCES Complaint (Complaint_Num, Complaint_Type)
);

Now open the table "Quality" in design view, select the column
"Complaint_Type", and use the one-word expression "quality" (including
the quotes) as its validation rule. Now you can

INSERT INTO Complaint VALUES (1, 'quality')
INSERT INTO Quality VALUES (1, 'quality')

You need some other constraints, too. But this illustrates how to
guarantee only one subtype row per supertype row.

 

[Tim Ferguson]

With the right structure, Jet can guarantee that, if a row exists in a
subtype table, there won't be corresponding rows in any other subtype
table.

So if I have a complaint (supertype) about quality (subtype), Jet can
guarantee the subtype data--*if* it exists--will be found only in the
subtype table "Quality".

I see what you mean, and I hadn't come across this before.

But Jet can't guarantee that subtype data
will exist.

Actually it couldn't be otherwise, because it would not be possible to
enter a new supertype record in the first place... except for what you said
about deferring constraint checking.

All the best


Tim F