Re: Simple linking table question

--CELKO-- wrote:
> These are relationship tables. Links implies physical pointers and
> not logic. It is a terminolgy from the pre-relational DB days. And
> your syntax was wrong.
>
> You need to decide on your data model first. Is this a three-way
> relationship like "buyer-seller-lender" or is another binary
> relationship like a location (longitude, latitude) and a person? Hre
> are some possible cases:
>
> one person is at only one place, a place holds only one person
>
> CREATE TABLE Sites
> (person VARCHAR(15) NOT NULL UNIQUE
> REFERENCES Personnel(person),
> long REAL NOT NULL,
> lat REAL NOT NULL,
> FOREIGN KEY (long, lat) REFERENCES Locations(long, lat),
> UNIQUE (long, lat));

Actually, *person is at most one place* (zero or one place) and *place holds at most one person* (zero or one person). The other three similar cases would be implemented as follows:

1. One person is at exactly one place, place holds no person or one person:

CREATE TABLE Locations (

	long REAL NOT NULL,
	lat REAL NOT NULL,
	CONSTRAINT pk_l PRIMARY KEY (long, lat));

CREATE TABLE Personnel (
	person VARCHAR(15) NOT NULL PRIMARY KEY,
	long REAL NOT NULL,
	lat REAL NOT NULL,
	FOREIGN KEY (long, lat) REFERENCES Locations(long, lat),
	UNIQUE(long, lat));

Table Sites not needed.

2) Place holds exactly one person, one person is at most one place (zero or one place):

CREATE TABLE Personnel (

        person VARCHAR(15) NOT NULL PRIMARY KEY);

CREATE TABLE Locations (

	long REAL NOT NULL,
	lat REAL NOT NULL,
	person VARCHAR(15) NOT NULL REFERENCES Personnel(person),
	PRIMARY KEY (long, lat),
	UNIQUE(person));

Table Sites not needed here, too.

3) Person is at exactly one place and place holds exactly one person:

CREATE TABLE PersonnelLocations (

	person VARCHAR(15) NOT NULL PRIMARY KEY,
	long REAL NOT NULL,
	lat REAL NOT NULL,
	UNIQUE (long, lat));

This is the only one table (instead of three tables: Personnel, Locations, Sites).

The idea behind this is to enforce "business rules" in the fastest, simplest and most naturall way. Obviously, in all these cases it is possible to use pattern with three tables (Personnel, Locations and Sites), but enforcing cardinalities would be slightly complicated (one approach would be to use CREATE ASSERTION).

> A place can have more than one person, a person is at only one place.
>
> CREATE TABLE Sites
> (person VARCHAR(15) NOT NULL UNIQUE
> REFERENCES Personnel(person),
> long REAL NOT NULL,
> lat REAL NOT NULL,
> FOREIGN KEY (long, lat) REFERENCES Locations(long, lat),
> PRIMARY KEY(person, long, lat)
> );

Similarly, if no other constraints are implemented person can be at no place at all, and place can have zero, one or more persons.

> A place can have more than one person, a person can be in more than
> one place.
>
> CREATE TABLE Sites
> (person VARCHAR(15) NOT NULL
> REFERENCES Personnel(person),
> long REAL NOT NULL,
> lat REAL NOT NULL,
> FOREIGN KEY (long, lat) REFERENCES Locations(long, lat),
> PRIMARY KEY(person, long, lat));

Person can be at zero, one or more places, and place can have zero, one or more persons.

-- 
Regards,
Damjan S. Vujnovic

University of Belgrade
School of Electrical Engineering
Department of Computer Engineering & Informatics
Belgrade, Serbia

http://galeb.etf.bg.ac.yu/~damjan/