re-designing & normalizing - best way to go about this?

Hi all,

Any advice will be appreciated here. I have been tasked with re-designing a database that, IMHO, is a mess. All of the data is in one table, fields:

Catalog_ID (PK),Class, Family, Genus/Species, Order , Authority, Description, Collection Site, County/Province, State/Country, Elevation, Geologic Age, Collector, Date Collected, Remarks

I am breaking this table up to try to achieve some normalization:

Table Species:
Species_ID (PK)
Class
Family
Genus
Species
Order
Authority

Table Locality:
Locality_ID (PK)
Collection_Site
County/Province
State/Country
Elevation
Geologic Age

Table Collector:
Collector_ID (PK)
F_Name
L_Name
Affiliation

Table Collection_Data:
Catalog_ID (PK)
Species_ID (FK references Species)
Locality_ID (FK references Locality)
Collector_ID (FK references Collector)
Description
Date Collected
Remarks

My questions are:
Does it look like I am achieving my goal? I began breaking down the main table and what I found was, for instance - in the Species table, I may have 200 of the same class, family, genus - and the species is different. Should I have a seperate table each for class, family, genus... my thinking is not, because the species is dependant on the other 3 fields, but when I look at 2500 entries in the species table and see class repeated over and over... I am not sure.

I need to design this database so other research projects can be added in the future, so I need to get it right when I start.

Thanks again, for any advice!
Dina Newsham,
Northern Arizona University Received on Thu Apr 11 2002 - 17:30:32 CDT