Table schema critique needed



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?

- 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

- 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

- Room numbers (PKey)
- Grade assigned

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

- 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!


- 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?)



Terri Pecora

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

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.



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question