normalization question

[grayth]

Hi everyone..

I'm trying to set up a civil war database that includes the various soliders information...divided into his various units and include what artifactes I have on record for that individual...

I have been working on the information and so far I'm stuck on the best way to apply normalization to this any help would be great.

here's a breakdown of what I have so far

Soldier Table

SoldierID# primary key
Last name
First Name
Date of Birth
City
State
Date Enlisted
Rank
Date of promotion
Assigned Military Unit
Function
Date of Death
Cause of Death
City
State
Notes
Type of Relic on Hand
Relic inventory #

Regiment Table

regiment id# primary Key
Regiment number
regiment state
Type of regiment
Assigned brigade secondary key

Brigade Table

Brigade id primary key
Brigade Name
Assigned Division secondary key

Division Table

divison ID primary key
Division Name
Assigned corps secondary key

corps table

corps id primary key
corps name
assigned army secondary key

Army Table

army id
army name
side

 

[Ziggy]

These are all related and should be fields , although I think the regiment may be the main type. I'm not familiar with how the structure (terminology)is set up.

I think this falls under "Military unit" so that is one table, but you may have it right, if you provide some sample data I could visualize it better

Regiment Table
Brigade Table
Division Table
corps table
Army Table

Also a


tblFunction (or value list)
tblRank (or value list)
tblCity
tblState



it is best to have a table for city, but if you can get one or build one from that period I'm sure it would be smaller. I only suggest it because a freeform city field is open to spelling errors and that will affect your grouping/sorting in reports.

You have city and state twice?