Re: Database design
From: Christo Yssel (agc_at_absamail.co.za)
Date: 03/16/04
- Next message: irvicky: "add new record in a auto-lookup query based form"
- Previous message: Douglas J. Steele: "Re: Multi Select list box and the bound column...."
- In reply to: Ed Warren: "Re: Database design"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 16 Mar 2004 11:34:42 -0800
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
"Ed Warren" <eowarren@alltel.net> wrote in message
news:OlU9BQ1CEHA.3748@TK2MSFTNGP11.phx.gbl...
> 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" <agc@absamail.co.za> wrote in message
> news:6eqdnTdHPZ51wdLdRVn-hQ@is.co.za...
> > 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
> >
> >
> >
> >
> >
> >
>
>
- Next message: irvicky: "add new record in a auto-lookup query based form"
- Previous message: Douglas J. Steele: "Re: Multi Select list box and the bound column...."
- In reply to: Ed Warren: "Re: Database design"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
