[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