One table or two?

[Guest]

I work at a university, and I’m working on building a database specifically
for our department. I’m taking Access classes (have taken 2 of 4 so far),
and have no other knowledge of building databases. This particular database
will keep track of people and events.

Right now, I’m concentrating on the people. Most of the people are
university faculty, staff or students. But there will also be some people
from outside of the university. Its been suggested that I use the university
ID as the primary key…for non-university people I should assign them a
sequential identifying number (in the same format as the university ID, but
starting with an “Nâ€).

Here’s the information I will have for university people:
University ID
First Name
Last Name
MI
Title
Suffix
College
Department
Rank
Date of hire
Phone number
Fax number
Email address

And for non-university people:
First name
Last name
MI
Company/University
Title
Phone number
Fax number
Email address

Should I just have one table for people in general, or should I have one
table for university people and one for non-university people?

 

[Guest]

If you use 2 tables, you would have find a way to link the tables together.

If you decide to put the information all together, you add another field
with a drop down menu.

Example:
Status = Faculty, staff, Student or other.

Most DBA create a separate table for Employees, Students.

For example: A hospital Database
Table 1: Patients
Table 2: Employees
Table 3: Visits

To have a better understanding you should take a Database Concept class. It
really helps. I took several Access courses but did not really understand a
database until I took a Database Concept course.

 

[Brendan Reynolds]

Check out Rebecca Riordan's "Sub-classing Entities" example at the following
URL ...
http://www.mvps.org/access/tables/tbl0013.htm

 

[Ed Warren]

I work with univerisity people and here are some thoughts.

Are you really sure you only want to capture only one each of the following:

email address, many professors have multiple email addresses. (one for
students in a course, another for other professors in the university, and
sometimes yet another for use outside the university.

department: some professors work with more than one college, department.

phone number: what about home, office, cell

date of hire has always been a problem for me in university data settings.
people get hired several times, have multiple jobs with the university

worst of all many students are staff and some professors are students.

If the answer to the above is no we need to have multiple entries for these
then you are into building a 'third normal form' set of tables and
relationships.

Lots of luck with your project. Warning, what now seems simple can quickly
become complex.

Ed Warren.