[thelist] DB Design question
Chris Brody
subscriptions at cbrody.com
Mon Sep 15 12:21:03 CDT 2003
Bob Haroche wrote:
> Assuming there will be a master of list of hundreds of
> ptotential breeds (all inactive at the start) from which the admin
> will make a few score active for regular use, what's a good way of
> designing the (Access) database holding this information.
>
> 1. Adding an active/inactive column to the existing AnimalBreeds
> table?
> 2. Creating an InactiveAnimalBreeds table and moving the records back
> and forth between the two?
> 3. Something else?
3. The active/inactive status is specific to the shelter, not to the breeds
table, so have a separate table which specifies which breeds are currently
available at each shelter.
Simplest solution:
One way is to use a string of 1s and 0s to indicate which breeds are
available at the shelter, e.g. the following shows that breeds 1, 4 and 6
are available:
Table: Shelter
ID Name BreedsAvail
1 Battersea Dogs Home 1001001
Better:
A more elegant solution is to have a separate BreedsAvail table for each
shelter (if a centralised system covering multiple shelters) with a row
inserted/deleted for each currently available breed, e.g.
Table: BreedsAvail_1
ID BreedID
1 1
2 4
3 6
Best solution (IMO!):
A lookup table with one row per shelter/breed combination. Either add &
delete rows as necessary, or use a separate field to indicate availability,
e.g.
Table: BreedsAvail
ID ShelterID BreedID NumberAvail
1 1 1 17
1 1 4 2
1 1 6 0
HTH.
--Chris.
www.cbrody.com
More information about the thelist
mailing list