Database design

[Christo Yssel]

Hi,
I created a small database. I just need to be sure that I have done it all
correctly. Any suggestions would be appreciated.
Scenario:
The database tracks information about farmers who attend auctions. The same
auction may take place more than once, lets say every three months, once a
year, twice maybe. Every auction may be of one or more than one type (Where
type is what is sold at the auction, ex. Breeding Cattle, Horses, Sheep,
Milk Cows). There is a many-to-many relationship between Type and Auction.
A farmer may attend one or more than one auction, but he can also attend the
same auction but on different dates. Farmers and auctions are in a
may-to-many relationship. I need the following reports: A list of all
farmers which attended an auction on a specific date. Whenever a auction is
scheduled we need to print mailing labels to send invitations to farmers,
but these labels are filtered by type. So if the type of the auction is
horses, labels will be printed for all farmers who attended horse type
auctions, but if the auctions is of two types Horses and Milk Cows, labels
must be printed to include both types. (I am not sure how I am going to
prevent duplicate labels in the second case, because a farmer may have
attended both types).
Tables:
Farmers
FarmerID (PK)
Surname
Name
Initials
Address
....
Active (Never delete farmer, set active to no when he does not attend
auctions any more)

FarmerAuctionDetails
FarmerID
AuctionID
Date (I have set the composite primary key to all three fields as the
farmer can attend the same auction but on different dates)

Auctions
AuctionID (PK)
Name
Description

AuctionTypeDetails
AuctionID (FK)
AuctionTypeID (FK) Composite primary key

AuctionTypes
AuctionTypeID
Type
Description

Thanks
Christo

 

[Rolls]

To prevent duplicate labels you need a WHERE clause specifying the multiple
criteria, and a GROUP BY clause that will eliminate duplicate query results
in the event that there would otherwise be multiple SELECT results returned.

The rest looks about right. Set up a separate table for each entity. Set
up a relationship table to reduce the complexity to 1:many.

 

[PC Datasheet]

The first thing I notice is that you have fields named Date and Name. Both of
these are reserved words and should not be used for field names; using them will
bring you problems sooner or later. Not sure about Typs but I would change that
one too.

 

[Chris]

Both the other posters had good ideas.

The flaw I see with your design is in the
FarmerAuctionDetails table.

If I read it right, the same auction will be on different
days, but those days are set. The FarmerAuctionDetails
table will not help you set which days those auctions are.

For example, a certain auction is help on the Jan 1st, Feb
1st, and Mar 1st. You could put in that a Farmer visited
this auction on Jan 2nd, which it was not held.


I would put an AuctionDates table between the Auctions and
FarmerAuctionDetails so that you are sure of data accuracy.


Chris.

 

[Christo Yssel]

I did think about what will happen if you enter a wrong date, but chose to
ignore it. I will rather cater for that, better save than sorry.

Thanks for all the help
Christo

 

[Christo Yssel]

I looked at this out of all angles and I am now stuck. There is one little
problem which may arize. What if 2 different auctions are scheduled on the
same date ? This might happen. This creates another many-to-many
relationship between Auctions and the new table AuctionDates. I could do
the following, I think:

Add Tables
AuctionDates
AuctionDateID (PK)
AuctionDate

AuctionDetails
AuctionID
AuctionDateID (Composite primary key)

This resolves the many-to-many relationship between Auctions and the Dates
Table, but how do I now connect the farmers to the dates?

 

[Ed Warren]

I love auctions and go frequently, so I know more about auctions that
databases. I try to look at the 'objects' in play first then build the
tables.

In your case we have the following:

Auction types : Breeding Cattle, Horses, Sheep, Milk Cows, ......
tblAuctionTypes
AuctionTypeID (PK)
Type
Description

Participants: Name, address, city, state, zip, telephone, email, .....
tblFarmers
FarmerID (PK)
Surname
Name
Initials
Address
Active (Never delete farmer, set active to no when he does not
attend auctions any more)


Auction Location: auction location (building, room, sale barn) in some city
tblAuctionLocation
AuctionLocationID (PK)
AuctionLocationFacility

Auction Role: buyer, seller, both
tblRole
RoleID (PK)
Role


Scheduled Auction: Auction Location, Auction Date (captures unique date and
place but not type)
tblAuctionSchedule
AuctionScheduleID (PK)
AuctionLocationID (FK)
AuctionDate


AuctionToSchedule: Auction Type and Scheduled Auction (allows you to have
multiple type of auction at same place/date)
tblAuctionTypeToSchedule
AuctionTypeToScheduleID (PK)
AuctionScheduleID (FK)
AuctionTypeID (FK)


tblAuctionTypeToSchedule 1-->M tblAuctionSchedule
tblAuctionTypeToSchedule 1--> M tblAuctionType
Could delete the AutionTypeToScheduleID key and use the two FK's as a
composite PK.

Auction Participants: People associated with a particular Scheduled Auction
(People at a place on a date)

tblAuctionParticipants
AuctionParticipantID (PK)
AuctionScheduleID (FK)
ParticipantID (FK)
AuctionRoleID (FK) to tblAuctionRole (only if you choose not to go
to the transaction level in the database)

Again could remove the PK and combine the two FK into a composite PK.

Auction Transactions: People associated with a particular Scheduled Auction
and one transaction Buy/sell, item, price. (Your data needs very well may
not require this level, but should be considered then rejected rather than
overlooked.

tblAuctionTransactions
tbleAuctionTransactionID (PK)
AuctionParticipantID (FK)
TransactionAmount currency
Buyer or Seller (yes/no field yes = buyer

The problem I see with your current design is tying the participant (farmer)
to the date rather than the scheduled auction.

Regards not getting duplicate mailing labels, you take care of that through
queries. First get the unique participantID for all the auctions of
interest, then query for the addresses of those participants.


Hope some of this makes sense and helps more than confuses

Cheers, and good luck

Ed Warren

 

[Christo Yssel]

Thanks for the reply
I looked at your design and I can see how to solve my problem. You are
right, your design has more than what I need now, but later it might (most
likely will be) more complex.

Christo