Table schema critique needed

  • Thread starter Thread starter Hera
  • Start date Start date
H

Hera

I'm developing my first from-scratch database for our
small (600 students/400 families) elementary school. This
is my preliminary structure. For brevity's sake I haven't
listed the individual ident fields but I believe they're
fairly well normalized at this point.

Before I go to far, I'm wondering if I'm heading in the
right direction?

Fam-Data
- PKey is Family ID (FID) which is manually derived
from first letter of primary parent last name and 10-digit
phone number
- misc ident data for primary and secondary parents in
discrete fields; have to allow for blended household data
- address data for pri & sec parents; ditto

Stu-Data
- PKey is auto-numbered student ID (SID)
- FKey to Fam-Data.FID
- student ident (name fields, DOB, grad year, med file)
- FKey to Rm-Data.RMNO

Rm-Data
- Room numbers (PKey)
- Grade assigned

Fac-Data
- Room number (FKey to Rm-Data.RID)
- Teacher ident fields

Med-Data
- FKey to Stu-Data.SID
- Still working on fields; table must hold student
medical info (privacy act demands password-protection)
such as meds taken at home, in school, allergies, etc.
This is my toughest design challenge at the moment.

Currently we're not tracking grade data but that will come
in phase 2 (next year) and we don't have a schedule or
room resource issue to track (elem school.)

Any comments or thoughts much appreciated - thanks!

Hera


L-Zip
- City, State and Zip
*This table isn't normalized because it repeats
the state in every record but since 100% of our families
are in the same state I can't see creating a state table
just for one field and record -- or should I?)
 
Looks like you are on the right track...
A few comments...

I don't know that your primary key on the Fam-Data Table
should contain the phone number. If someone moves and
that changes then you will have to change in a couple
tables or ensure your relationship is setup with a cascade
update. You will probably be better off with an
autonumber.

There is no reason to have a separate table for the city,
state, zip. Just include the data it in the address, or
columns in the Fam-Data table. Even if they are the same
for every family, its easier to have it in one place for
example if you want to create mailing labels.

Thanks
Terri


Thanks
Terri
 
Back
Top