-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
The Contracts table should have a ContractID (AutoNumber if you like).
The relations would be like this:
Sellers - 1:m -> Contracts <- m:1 - Auctions
Contracts - 1:m -> Lots
In DDL (JET 3.x):
CREATE TABLE Sellers (
SellerID COUNTER ,
LastName VARCHAR(40) NOT NULL ,
FirstName VARCHAR(20) ,
... other columns ... ,
CONSTRAINT PK_Sellers PRIMARY KEY (LastName)
)
CREATE UNIQUE INDEX idx_Sellers ON Sellers (SellerID)
CREATE TABLE Auctions (
AuctionID COUNTER ,
SaleNumber INTEGER NOT NULL ,
SaleDate DATE NOT NULL ,
Location VARCHAR(50) NOT NULL ,
... other columns ... ,
CONSTRAINT PK_Auctions PRIMARY KEY (SaleNumber, SaleDate, Location)
)
CREATE UNIQUE INDEX idx_Auctions ON Auctions (AuctionID)
The primary key for Auctions could be just the SaleNumber, if each
SaleNumber should be unique. IOW, there cannot be the same SaleNumber
on different SaleDates, Locations.
CREATE TABLE Contracts (
ContractID COUNTER ,
AuctionID INTEGER NOT NULL REFERENCES Auctions ,
SellerID INTEGER NOT NULL REFERENCES Sellers ,
ContractDate DATE NOT NULL ,
... other columns ... ,
CONSTRAINT PK_Contracts
PRIMARY KEY (AuctionID, SellerID, ContractDate)
)
CREATE UNIQUE INDEX idx_Contracts ON Contracts (ContractID)
Here is where the relationships are defined between Contracts and
Auctions & Sellers.
The primary key could be just the AuctionID & SellerID if you allow more
than one record per ContractDate for the same Auction & Seller.
CREATE TABLE Lots (
AuctionID INTEGER NOT NULL REFERENCES Auctions ,
SellerID INTEGER NOT NULL REFERENCES Sellers ,
Descrip VARCHAR(500) NOT NULL ,
... other columns ... ,
CONSTRAINT PK_Lots PRIMARY KEY (AuctionID, SellerID)
)
Here is where the relationships are defined between Lots and Auctions &
Sellers.
If the Auctions.SellerNumber is unique then you can use the AuctionID as
the referrence to the SalesNumber in the Auctions table.
It may be that the SalesNumber and SalesDate should be in the Lots table
'cuz it seems the Auctions table should only have info about where &
when the Auction takes place. Therefore, the sale of each lot should
have the SalesNumber & the SalesDate in the same place as the Lot
information - the Lots table. That is my understanding of auctions - it
may be incorrect.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQUIiGYechKqOuFEgEQJEMgCghBbJZwTn5eo2RjBStGwht5 w1R/IAoLDc
ZTt+n5IHWXpC213xjZCmLBje
=VCwe
-----END PGP SIGNATURE-----
Mark wrote:
A marketing company conducts auctions.
For each auction sale, there is a table (AUCTIONS)listing the auction
sale number, date, location, etc.
Another table (SELLERS)contains our client list (seller name, contact,
phone number, etc).
Information is directly input into the AUCTIONS and SELLERS tables.
A third table (CONTRACTS) should contain contract information (sale
number from the AUCTIONS table, seller name from the SELLERS table,
contract date, etc. The sale number and seller name are selected from
combo boxes.
A fourth table (LOTS) contains information for each lot like sale
number, seller name, property description, etc. There are multiple
lots for each seller in each sale.
How should the relations be setup?
