Relationship Question

J

Jürgen Germonpré

Hi All,

I have a question about relationships. I'm not so good at designing
databases, just trying to get better.

This is the situation:
I have several objects: Mail, Depot and Action.
Each Mail can have 1 or more Actions, which are specific for the Mail object
(f.i. create, delete, check).
On the other hand a Depot can have 1 or more Actions, which are specific
for the Depot object BUT NOT the same as the Mail object (f.i. create,
delete, validate, update).

For this I've set up a table Mails (PK MailID), MailActions (PK
MailActionID, FK MailID) and Depots (PK DepotID), DepotActions (PK
DepotActionID, FK DepotID).
I've splitted up the Actions because actions for Mail and Depot are quite
distinct.
So far so good.

Now comes the fuzzy part: I have to create a relationship between Mail and
Depot and the two possibilities are the following.

One situation is that the Mail is the master: so one or many Depots can
point towards one Mail.
Mail1 <- Depot1
<- Depot2
or
Mail2 <- Depot3


Second situation is that the Depot is the master: so one or many Mails can
point towards one Depot
Depot4 <- Mail3
<- Mail4
or
Depot5 <- Mail5


I thought to set up a intermediate table MasterSlaveRelation with a
combined PK: FK MailID, FK Depot ID.
But that doesn't satisfy the need. Because once a Mail or a Depot has been
set as Master it can only occur once !
So for instance: Depot4 <- Mail1 is not valid ! although its occurence will
be accepted in the table.

A clue ?
Thanks a lot.

JG
 
J

Jamie Collins

This is the situation:
I have several objects: Mail, Depot and Action.
Each Mail can have 1 or more Actions, which are specific for the Mail object
(f.i. create, delete, check).
On the other hand a Depot can have 1 or more Actions, which are specific
for the Depot object BUT NOT the same as the Mail object (f.i. create,
delete,validate, update).

For this I've set up a table Mails (PK MailID), MailActions (PK
MailActionID, FK MailID) and Depots (PK DepotID), DepotActions (PK
DepotActionID, FK DepotID).
I've splitted up the Actions because actions for Mail and Depot are quite
distinct.
So far so good.

Now comes the fuzzy part: I have to create a relationship between Mail and
Depot and the two possibilities are the following.

One situation is that the Mail is the master: so one or many Depots can
point towards one Mail.
Mail1 <- Depot1
<- Depot2
or
Mail2 <- Depot3

Second situation is that the Depot is the master: so one or many Mails can
point towards one Depot
Depot4 <- Mail3
<- Mail4
or
Depot5 <- Mail5

I thought to set up a intermediate table MasterSlaveRelation with a
combined PK: FK MailID, FK Depot ID.
But that doesn't satisfy the need. Because once a Mail or a Depot has been
set as Master it can only occur once !
So for instance: Depot4 <- Mail1 is not valid ! although its occurence will
be accepted in the table.

A clue ?
Click to expand...

Do a google search on subclassing in SQL. Using such an approach, you
would have a superclass that comprises both mail and depot entities; I
have no idea what such an entity could be so I'll use my default name
of 'Trevor' (the following is Access/Jet SQL in ANSI-92 Query Mode):

CREATE TABLE Trevors (
trevor_name VARCHAR(15) NOT NULL UNIQUE,
trevor_type VARCHAR(5) NOT NULL,
CHECK (trevor_type IN ('Mail', 'Depot')),
UNIQUE (trevor_type, trevor_name)
)
;
CREATE TABLE Depots (
trevor_name VARCHAR(15) NOT NULL UNIQUE,
trevor_type VARCHAR(5) NOT NULL,
CHECK (trevor_type = 'Depot'),
FOREIGN KEY (trevor_type, trevor_name)
REFERENCES Trevors (trevor_type, trevor_name)
ON DELETE CASCADE
ON UPDATE NO ACTION
)
;
CREATE TABLE Mail (
trevor_name VARCHAR(15) NOT NULL UNIQUE,
trevor_type VARCHAR(5) NOT NULL,
CHECK (trevor_type = 'Mail'),
FOREIGN KEY (trevor_type, trevor_name)
REFERENCES Trevors (trevor_type, trevor_name)
ON DELETE CASCADE
ON UPDATE NO ACTION
)
;
CREATE TABLE MasterSlaveTrevors (
master_trevor_name VARCHAR(15) NOT NULL,
master_trevor_type VARCHAR(5) NOT NULL,
FOREIGN KEY (master_trevor_type, master_trevor_name)
REFERENCES Trevors (trevor_type, trevor_name)
ON DELETE CASCADE
ON UPDATE NO ACTION,
slave_trevor_name VARCHAR(15) NOT NULL UNIQUE,
slave_trevor_type VARCHAR(5) NOT NULL,
FOREIGN KEY (slave_trevor_type, slave_trevor_name)
REFERENCES Trevors (trevor_type, trevor_name)
ON DELETE CASCADE
ON UPDATE NO ACTION,
UNIQUE (master_trevor_type, master_trevor_name, slave_trevor_type,
slave_trevor_name),
CHECK (master_trevor_name <> slave_trevor_name)
)
;

And some test data:
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Depot1',
'Depot')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Depot2',
'Depot')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Depot3',
'Depot')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Depot4',
'Depot')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Depot5',
'Depot')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Mail1',
'Mail')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Mail2',
'Mail')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Mail3',
'Mail')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Mail4',
'Mail')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Mail5',
'Mail')
;
INSERT INTO Depots (trevor_name, trevor_type) VALUES ('Depot1',
'Depot')
;
INSERT INTO Depots (trevor_name, trevor_type) VALUES ('Depot2',
'Depot')
;
INSERT INTO Depots (trevor_name, trevor_type) VALUES ('Depot3',
'Depot')
;
INSERT INTO Depots (trevor_name, trevor_type) VALUES ('Depot4',
'Depot')
;
INSERT INTO Depots (trevor_name, trevor_type) VALUES ('Depot5',
'Depot')
;
INSERT INTO Mail (trevor_name, trevor_type) VALUES ('Mail1', 'Mail')
;
INSERT INTO Mail (trevor_name, trevor_type) VALUES ('Mail2', 'Mail')
;
INSERT INTO Mail (trevor_name, trevor_type) VALUES ('Mail3', 'Mail')
;
INSERT INTO Mail (trevor_name, trevor_type) VALUES ('Mail4', 'Mail')
;
INSERT INTO Mail (trevor_name, trevor_type) VALUES ('Mail5', 'Mail')
;
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Mail1', 'Mail', 'Depot1', 'Depot')
;
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Mail1', 'Mail', 'Depot2', 'Depot')
;
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Mail2', 'Mail', 'Depot3', 'Depot')
;
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Depot4', 'Depot', 'Mail3', 'Mail')
;
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Depot4', 'Depot', 'Mail4', 'Mail')
;

So far so good.

Your problem is this:

INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Depot5', 'Depot', 'Mail1', 'Mail')
;

Mail1 should be prevented from being a slave because it is a master. A
simple fix to such a design is a table-level CHECK constraint:

DELETE FROM MasterSlaveTrevors
WHERE master_trevor_name = 'Depot5'
;
ALTER TABLE MasterSlaveTrevors ADD
CONSTRAINT master_cannot_be_slave
CHECK (NOT EXISTS (
SELECT *
FROM MasterSlaveTrevors AS M1,
MasterSlaveTrevors AS M2
WHERE M1.master_trevor_name = M2.slave_trevor_name))
;

Jamie.

--
 
J

Jürgen Germonpré

Dear Jamie,

Thanks a lot for your response.
I've been busy on another project and couldn't check out your solution right
away.

I've automated the executing of the sql statements you sent me. While
testing the first statement created the Trevors table and now I want to
delete it but when doing so i get this error:

DDL cannot be completed on this table because it is referenced by constraint
<name> on table <name>. (Error 3803)
This is an unexpected error. Please contact Microsoft Product Support
Services for more information.

And so I can't delete the table...

What must I do prior to deleting the table ? I've already deleted the
indexes but this doesn't makes a difference...

Thanks.

JG



This is the situation:
I have several objects: Mail, Depot and Action.
Each Mail can have 1 or more Actions, which are specific for the Mail
object
(f.i. create, delete, check).
On the other hand a Depot can have 1 or more Actions, which are specific
for the Depot object BUT NOT the same as the Mail object (f.i. create,
delete,validate, update).

For this I've set up a table Mails (PK MailID), MailActions (PK
MailActionID, FK MailID) and Depots (PK DepotID), DepotActions (PK
DepotActionID, FK DepotID).
I've splitted up the Actions because actions for Mail and Depot are quite
distinct.
So far so good.

Now comes the fuzzy part: I have to create a relationship between Mail and
Depot and the two possibilities are the following.

One situation is that the Mail is the master: so one or many Depots can
point towards one Mail.
Mail1 <- Depot1
<- Depot2
or
Mail2 <- Depot3

Second situation is that the Depot is the master: so one or many Mails can
point towards one Depot
Depot4 <- Mail3
<- Mail4
or
Depot5 <- Mail5

I thought to set up a intermediate table MasterSlaveRelation with a
combined PK: FK MailID, FK Depot ID.
But that doesn't satisfy the need. Because once a Mail or a Depot has been
set as Master it can only occur once !
So for instance: Depot4 <- Mail1 is not valid ! although its occurence
will
be accepted in the table.

A clue ?
Click to expand...

Do a google search on subclassing in SQL. Using such an approach, you
would have a superclass that comprises both mail and depot entities; I
have no idea what such an entity could be so I'll use my default name
of 'Trevor' (the following is Access/Jet SQL in ANSI-92 Query Mode):

CREATE TABLE Trevors (
trevor_name VARCHAR(15) NOT NULL UNIQUE,
trevor_type VARCHAR(5) NOT NULL,
CHECK (trevor_type IN ('Mail', 'Depot')),
UNIQUE (trevor_type, trevor_name)
)
;
CREATE TABLE Depots (
trevor_name VARCHAR(15) NOT NULL UNIQUE,
trevor_type VARCHAR(5) NOT NULL,
CHECK (trevor_type = 'Depot'),
FOREIGN KEY (trevor_type, trevor_name)
REFERENCES Trevors (trevor_type, trevor_name)
ON DELETE CASCADE
ON UPDATE NO ACTION
)
;
CREATE TABLE Mail (
trevor_name VARCHAR(15) NOT NULL UNIQUE,
trevor_type VARCHAR(5) NOT NULL,
CHECK (trevor_type = 'Mail'),
FOREIGN KEY (trevor_type, trevor_name)
REFERENCES Trevors (trevor_type, trevor_name)
ON DELETE CASCADE
ON UPDATE NO ACTION
)
;
CREATE TABLE MasterSlaveTrevors (
master_trevor_name VARCHAR(15) NOT NULL,
master_trevor_type VARCHAR(5) NOT NULL,
FOREIGN KEY (master_trevor_type, master_trevor_name)
REFERENCES Trevors (trevor_type, trevor_name)
ON DELETE CASCADE
ON UPDATE NO ACTION,
slave_trevor_name VARCHAR(15) NOT NULL UNIQUE,
slave_trevor_type VARCHAR(5) NOT NULL,
FOREIGN KEY (slave_trevor_type, slave_trevor_name)
REFERENCES Trevors (trevor_type, trevor_name)
ON DELETE CASCADE
ON UPDATE NO ACTION,
UNIQUE (master_trevor_type, master_trevor_name, slave_trevor_type,
slave_trevor_name),
CHECK (master_trevor_name <> slave_trevor_name)
)
;

And some test data:
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Depot1',
'Depot')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Depot2',
'Depot')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Depot3',
'Depot')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Depot4',
'Depot')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Depot5',
'Depot')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Mail1',
'Mail')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Mail2',
'Mail')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Mail3',
'Mail')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Mail4',
'Mail')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Mail5',
'Mail')
;
INSERT INTO Depots (trevor_name, trevor_type) VALUES ('Depot1',
'Depot')
;
INSERT INTO Depots (trevor_name, trevor_type) VALUES ('Depot2',
'Depot')
;
INSERT INTO Depots (trevor_name, trevor_type) VALUES ('Depot3',
'Depot')
;
INSERT INTO Depots (trevor_name, trevor_type) VALUES ('Depot4',
'Depot')
;
INSERT INTO Depots (trevor_name, trevor_type) VALUES ('Depot5',
'Depot')
;
INSERT INTO Mail (trevor_name, trevor_type) VALUES ('Mail1', 'Mail')
;
INSERT INTO Mail (trevor_name, trevor_type) VALUES ('Mail2', 'Mail')
;
INSERT INTO Mail (trevor_name, trevor_type) VALUES ('Mail3', 'Mail')
;
INSERT INTO Mail (trevor_name, trevor_type) VALUES ('Mail4', 'Mail')
;
INSERT INTO Mail (trevor_name, trevor_type) VALUES ('Mail5', 'Mail')
;
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Mail1', 'Mail', 'Depot1', 'Depot')
;
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Mail1', 'Mail', 'Depot2', 'Depot')
;
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Mail2', 'Mail', 'Depot3', 'Depot')
;
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Depot4', 'Depot', 'Mail3', 'Mail')
;
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Depot4', 'Depot', 'Mail4', 'Mail')
;

So far so good.

Your problem is this:

INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Depot5', 'Depot', 'Mail1', 'Mail')
;

Mail1 should be prevented from being a slave because it is a master. A
simple fix to such a design is a table-level CHECK constraint:

DELETE FROM MasterSlaveTrevors
WHERE master_trevor_name = 'Depot5'
;
ALTER TABLE MasterSlaveTrevors ADD
CONSTRAINT master_cannot_be_slave
CHECK (NOT EXISTS (
SELECT *
FROM MasterSlaveTrevors AS M1,
MasterSlaveTrevors AS M2
WHERE M1.master_trevor_name = M2.slave_trevor_name))
;

Jamie.

--
 
J

Jamie Collins

created the Trevors table and now I want to
delete it but when doing so i get this error:

DDL cannot be completed on this table because it is referenced by constraint
<name> on table <name>. (Error 3803)

What must I do prior to deleting the table ? I've already deleted the
indexes but this doesn't makes a difference...
Click to expand...

Table-level CHECK constraints must be dropped first e.g.

ALTER TABLE MasterSlaveTrevors DROP
CONSTRAINT master_cannot_be_slave
;

FWIW the SQL-92 standard has such syntax

DROP TABLE MasterSlaveTrevors CASCADE
;

which would remove the dependencies but although Access/Jet supports**
the syntax it doesn't perform the functionality (**that is, it doesn't
error; you can replace the keyword 'CASCADE' with any other word and
it won't error either <g>!)

Jamie.

--
 
J

Jürgen Germonpré

Thank you once again for your response.

Just after i've posted this.
I've realized I didn't specify this:
I've used your SQL statements by passing them into an connection.Execute
statement in ado.
I've tried to delete the table in the Tables window of Access, just select
and press delete.... that generated the error.
I'm not such an ace in these things but I've tought: what if I do it the
same way as I've created the table: passing "Drop Table Trevors" into the
connection.Execute. That worked alright....
Nevertheless, thanks for your info.

Now, what confuses me a bit is that obviously if you're manipulating a .mdb
through ADO it doesn't result in the same situation as if you would do the
same thing by means of the Access table/query design interface.
Or am I missing a point here ?


JG

created the Trevors table and now I want to
delete it but when doing so i get this error:

DDL cannot be completed on this table because it is referenced by
constraint
<name> on table <name>. (Error 3803)

What must I do prior to deleting the table ? I've already deleted the
indexes but this doesn't makes a difference...
Click to expand...

Table-level CHECK constraints must be dropped first e.g.

ALTER TABLE MasterSlaveTrevors DROP
CONSTRAINT master_cannot_be_slave
;

FWIW the SQL-92 standard has such syntax

DROP TABLE MasterSlaveTrevors CASCADE
;

which would remove the dependencies but although Access/Jet supports**
the syntax it doesn't perform the functionality (**that is, it doesn't
error; you can replace the keyword 'CASCADE' with any other word and
it won't error either <g>!)

Jamie.

--
 
J

Jamie Collins

what confuses me a bit is that obviously if you're manipulating a .mdb
through ADO it doesn't result in the same situation as if you would do the
same thing by means of the Access table/query design interface.
Or am I missing a point here ?
Click to expand...

You can put the interface into ANSI-92 Query Mode (a.k.a. SQL Server
compatibility mode) to be able to execute the SQL DDL from a Query
window. ADO always uses ANSI-92 Query Mode for Jet, just as DAO always
uses ANSI-89 ('traditionally') Query Mode. Posting ANSI-92 Query Mode
SQL DDL is easier for me than posting the equivalent 'steps to
reproduce' using the interface.

Note that the user interface designers lag behind the enhancements to
the engine for Jet 4.0 by nearly a decade (!!) e.g. for table-level
CHECK constraints there is no expression builder and one created via
SQL code does not show in the table designer etc.

Jamie.

--
 
J

Jürgen Germonpré

Hello,

That's clear.

Thank you very much.

JG

what confuses me a bit is that obviously if you're manipulating a .mdb
through ADO it doesn't result in the same situation as if you would do the
same thing by means of the Access table/query design interface.
Or am I missing a point here ?
Click to expand...

You can put the interface into ANSI-92 Query Mode (a.k.a. SQL Server
compatibility mode) to be able to execute the SQL DDL from a Query
window. ADO always uses ANSI-92 Query Mode for Jet, just as DAO always
uses ANSI-89 ('traditionally') Query Mode. Posting ANSI-92 Query Mode
SQL DDL is easier for me than posting the equivalent 'steps to
reproduce' using the interface.

Note that the user interface designers lag behind the enhancements to
the engine for Jet 4.0 by nearly a decade (!!) e.g. for table-level
CHECK constraints there is no expression builder and one created via
SQL code does not show in the table designer etc.

Jamie.

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

FWT Newsletter - Weekly - September 6, 2004 1

Top