table design

  • Thread starter Thread starter SITCFanTN
  • Start date Start date
S

SITCFanTN

I'm building a new database and making the following tables: tblStudents,
tblTeachers, and tblAdministrators. Many of the filed s in these tables are
the same, for example name, address, city, state, zip, phone.....do I need to
specify these fields differently in each table, for example StudName,
StudAdd, StudCity, and TeachName, TeachAdd and TeachCity or is it acceptable
to just name them the same in all tables thinking the tables will
differentiate them? Thanks
 
I'm building a new database and making the following tables: tblStudents,
tblTeachers, and tblAdministrators. Many of the filed s in these tables are
the same, for example name, address, city, state, zip, phone.....do I need to
specify these fields differently in each table, for example StudName,
StudAdd, StudCity, and TeachName, TeachAdd and TeachCity or is it acceptable
to just name them the same in all tables thinking the tables will
differentiate them? Thanks
Click to expand...

The tables will differentiate them... but perhaps you could consider another
suggestion.

People may have multiple addressed; many people have multiple phones (home,
work, cell, pager, fax...)

You may want to consider a structure like

tblPeople
PersonID <Primary Key, autonumber or other unique ID>
LastName
FirstName
<other purely biographical info>

tblAddresses
AddressID <Primary Key>
PersonID <whose address>
AddressNo
Street
Direction
City
State
Nation
PostCode

tblPhones
PhoneID
Phone <Text>
PersonID
PhoneType

Your Students, Teachers and Administrators tables could then have just a
PersonID linking to the information in these other tables (using queries
and/or subforms and/or combo boxes), together with information relevant to
that person's role as a teacher or whatever. (Bear in mind that a person might
be a teacher in one classroom and a student in another, either at different
times or concurrently; and that someone might be wearing both a teacher and
administrator hat).

John W. Vinson [MVP]
 
Back
Top