How to organise tables?

[Jane]

I am doing a database for an educational organisation.
Originally I did 1 table for students and one for staff.
However it turns out that some staff also like to attend
courses. At first I thought I would just copy over the
staff member's name, address and other details to the
student table. But this isn't very neat.

Does it make sense to have one table for 'persons', say,
(with name and contact details) and two more tables for
staff (with qualifications, Christams card list and extra
stuff required for staff only) and for students (with the
extra stuff required for students only)?

I'd be grateful for any help.

Thanks

Jane

 

[Ken Snell]

Suggestion:

tblPersons ( for everyone )
PersonID (primary key)
PersonFirstName
PersonLastName

tblStudents
StudentID (primary key)
PersonID (foreign key to tblPersons)
StudentClassification
(etc.)

tblStaff
StaffID (primary key)
PersonID (foreign key to tblPersons)
StaffClassification
(etc.)

 

[Tim Ferguson]

tblStudents
StudentID (primary key)
PersonID (foreign key to tblPersons)
StudentClassification
(etc.)

tblStaff
StaffID (primary key)
PersonID (foreign key to tblPersons)
StaffClassification

Ken; what is the point of the StaffID and StudentID fields? One assumes
here that there are going to be 1:1 relationships with each of these and
tblPersons, so there will have to be a unique index made on the FK fields
in any case. Have I missed some reason for there being an advantage in
having the extra keys?

All the best


Tim F

 

[Ken Snell]

The separate StudentID and StaffID fields that I posted may not be needed if
the person would want to use the PersonID as the unique, primary key in the
tblPersons table. I sometimes add the extra primary key to table structures
so that I don't "overlook" setting up the primary key; other times, I'll use
an existing field.

So, you're right... the two extra fields could be left out!

 

[Tim Ferguson]

The separate StudentID and StaffID fields that I posted may not be
needed if the person would want to use the PersonID as the unique,
primary key in the tblPersons table. I sometimes add the extra primary
key to table structures so that I don't "overlook" setting up the
primary key; other times, I'll use an existing field.

So, you're right... the two extra fields could be left out!

Sorry to be rude and not reply: I just moved house and was off line until
today.

I just wanted to check if there was something I was missing -- always feel
insecure when I don't agree with am MVP!!

All the best


Tim F

 

[Ken Snell]

I just wanted to check if there was something I was missing -- always feel
insecure when I don't agree with am MVP!!

< g > me too!

 

[Dave]

When a staff person becomes a "student", put them into the
student table, and enter the corresponding course info as
you would for any student. Only a minimal amount of
personnel info would need to be entered into
their "student" record, since you probably have their
primary information in your Staff table already.

For both Staff and Students, you could have another table
for each, with just misc info. Info you don't want to
keep with their primary personnel record.