Database design

[BeetleBailey]

I am creating a database from several Excel spreadsheets used by a school. The Excel spreadsheets are:

1. Students' contact information, emergency contact, immunizations, parent contact info, etc.
2. Faculty information
3. Alumni information
4. Interested parties (mailing list of non-student, non-faculty, non-alumni, non-family members)

I understand the process of importing the Excel files into Access. What confuses me is the number of tables I need to keep the information in my tables unique and accurate when you have several students with the same address, emergency contacts and/or parents; faculty that are also parents and alumni; etc. I've read so much on database design my head is swimming. I would appreciate some input. Thanks.

[June7]

I presume this is not college level so you won't have students who are also staff or alumni? What to you mean by alumni - former students? For K-12?

You have to decide how far to carry out data normaliztion. For instance, could just enter all address info in every record, even if sibling and adult records would have same address info. Or you can have an Addresses table where you enter an address once with a unique ID key that would serve as a foreign key in the Students/Parents/Faculty/Alumni tables. A complication arises with apartment/condo type residences where the building and street are the same but a unit number makes the address unique. Possibly means repetitive building and streets and a lot of empty UnitNo fields.

Then there are all these people entities - students, parents, faculty, alumni. Could have one table for all People with a unique ID and name. Then child tables for Students, Parents, Faculty, Alumni, with foreign key field for the PeopleID. Or keep students in one table and all the adults (parents, faculty, alumni) in another. Then decide whether to have check boxes to indicate if each adult has parent, faculty, or alumni affiliation or have child tables.

[pdebaets]

Just some ideas for you...

Enter all the information about People in the same table and include yes/no fields for "IsStudent", "IsParent", "IsFaculty", etc... This will allow a person to be in more than one group/category.

Enter all the address information in one Addresses table then link the address record to the person in the People table. Then you won't need to keep duplicate addresses.

[BeetleBailey]

Thank you for your reply. Yes, this database is for K-12 grades and alumni are past students. Since Student is the only unique designation, i.e. a student cannot be a parent, faculty, or alumni, I am leaning towards creating a Students table and then another table for all other designations (parent, faculty, alumni, interested party, etc.) and use foreign keys in the Student table to reference relationships in the other table. Am I on the right track??

[BeetleBailey]

Then there are all these people entities - students, parents, faculty, alumni. Could have one table for all People with a unique ID and name. Then child tables for Students, Parents, Faculty, Alumni, with foreign key field for the PeopleID. Or keep students in one table and all the adults (parents, faculty, alumni) in another. Then decide whether to have check boxes to indicate if each adult has parent, faculty, or alumni affiliation or have child tables.

I am not familiar with "child tables". Can you give me a description please?

[June7]

An example would be a Sales database. Tables for customers, products, orders, order details.

Order details would be child to orders. Orders table would have order number, order date, customer ID. Order details would list the product IDs and quantities ordered. This is one-to-many relationship. Can't enter order details without an order record.

In your db, a People table would have info that any person would have - name, birthdate, etc. Then 'child' tables would have data specific to the person type.
Students: parent contacts, etc. Also maybe other dependent tables for classes/grades and immunization dates.
Faculty: position number, pay grade, etc.
Alumni: whatever data you need that is not already in the People and Student tables.

[pdebaets]

You can certainly have a separate Student table, if you wish and if you think that will be the best approach for you. Just some thoughts (you can take these with a grain of salt)...

With a "People" table that holds all, you can create a query

Select * from People where IsStudent;

... that will effectively serve as a "Student table".

With a separate table for Students and for all other people, you will have duplicate attributes/field names (Last Name, First Name, MI, etc...) in your database. This might be OK, but you will likely have to do some work to ensure consistency between these duplicate attributes. Ex.: If you change the length of the Last Name field in the Students table, you probably also want to change it in the "all other people" table. 2X work.

If you build a form that searches for people, you'll need to do the search on two tables instead of one.

Just my 2 cents.