Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Simple linking table question

Re: Simple linking table question

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 20 Mar 2003 15:09:31 -0800
Message-ID: <c0d87ec0.0303201509.3aba6547@posting.google.com>


>> Now I want to make tableX, where tableX is a linking table that
joins a
single-PK table and a two-column-CPK linking table. How should this tableX look? <<

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

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

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)); Received on Thu Mar 20 2003 - 17:09:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US