By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,985 Members | 1,694 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,985 IT Pros & Developers. It's quick & easy.

Tables and Relationships

P: n/a
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?
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
-----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?


Nov 13 '05 #2

P: n/a
mr****@ogclearinghouse.com (Mark) wrote in message news:<9b**************************@posting.google. com>...
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?


You're starting at the wrong place. You need to describe the PROCESS,
and NOT the tables. Do the table stuff later when you know what
entities you're dealing with. Something like

A Person (acting as SELLER) has something he wants to sell... He can
take many bids on the object.... etc

See? NO reference whatsoever to any database things at all. Start
there, and if you do it right, the database design should fall out.
But first things first. Get the story down. Tables come later once
you understand the "story".
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.