Database design: Setting up a staff list with departments and subdepartments

Question

Database design: Setting up a staff list with departments and subdepartments

Anonymous

Hi folks;

I'm doing a database project that is considerably more complex than the simple ones I've tackled before and I could really use some help.

The project is to create a staff directory for my workplace (a university library group). I am trying to recreate an older document that was not a database, just text in Word, arranged by hand in five different ways, and that had gotten riddled with errors over the years due to inconsistent and incomplete updates -- things like old staffers still on the list, new staffers NOT on the list, unrecorded email or phone changes and the like.

Anyway, to combat the creeping rot in the staff directory, I proposed recreating it as an Access database with reports, such that when staff changes happened, a single edit to the database would reflect the changes throughout the various reports.

However, I've run into a snag. Essentially, the staff directory exists as five separate documents collated into one printable document, with each document featuring a different subset of staff info. The first section has employees listed alphabetically by last name -- simple enough. Then another one has the staff listed by department. Also fairly simple.

Now, however, I have to recreate a document that is similar to the second one -- it's by department -- but it is ALSO broken down by SUBDEPARTMENT within the department. For example, we have a Technical Services department that has employees under that listing, but there are more employees under Technical Services: Acquisitions & Monographs, and then under Technical Services: Cataloging, and so forth.

Now, I imported all the base employee data, including their departments and subdepartments, from Excel into Access. Then I tried to import the Excel sheet that held the department listings themselves, because this staff list also has departmental emails and phone and fax numbers that are not tied to individual employees. So the final document looks kind of like this:

Technical Services ******@univ.edu x7-5644

Doe, Jane Department Head x 7-5623

Cataloging ******@univ.edu x7-5614

Roe, Martin Senior Staff x7-5611

Coe, Cathy Staff x7-5610

Loe, Larry Staff (Student) x7-5608

Now my main problem is that I have two separate chunks of data to coordinate here and I don't know quite how to do it. I need a table containing a list of departments and subdepartments with the department contact info (phone, fax, email) but I also need to be able to sort out the employees with THEIR data into departments and subdepartments. I have been using the primary key on the employee list as my main organizer for this project, but I'm wondering if I should make it by department instead? When I imported the department/subdepartment spreadsheet into the database as a table, I wound up with it being keyed on the Department, but with each subdepartment having a separate index (such that, for example, Technical Services would be entry 1, and Technical Services: Cataloging would be entry 2, and Technical Services: Serial Resources would be entry 3. That seems wrong to me, but I don't know what to do about it. I mean, ideally the departments should be indexed individually (Technical Services is one department, Operations is another, Systems is a third) and then the subdepartments are indexed under that, such that Technical Services has its three subdepartments, Operations has its two, and Systems has no subdepartments.

I think there must be a better (more elegant and streamlined) way to coordinate this database and still preserve the functionality I need to generate the variously-sorted staff directories, but I'm not sure what to do or how to best set things up.

Can anyone tell me how best to do this? Any suggestions would be appreciated!

-- TwinOwl

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Answer 1

You are approaching this from the wrong direction. Trying to build a database to recreate output is wrong. First design your data, THEN work on output.

So, I see at least three tables:

Staff

StaffID (PK Autonumber)

Firstname

Lastname

Extension

TitleID (FK)

DeptID (FK)

Department

DeptID (PK Autonumber)

Department

Title

TitleID (PK Autonumber)

Title

From there you should be able to reproduce your listing.

Answer 2

So you have two of your tables to start with. You should add Autonumber Primary keys to each table. Employee name should be broken out into at least first and last names. Generally I use 5 fields for people names (Salutation {Mr, Ms, etc.}, First, Middle, Last, Suffix {Jr, Sr, etc.})

The department field in the Employee table should be linked to the PK field in the Department table.

Your department table should have a Subdepartment field that is linked back to the DepartmentID.

Answer 3

You are approaching this from the wrong direction. Trying to build a database to recreate output is wrong. First design your data, THEN work on output.

This I know. However, although I do plan to approach Da Boss when she returns from vacation next week and suggest that we rethink exactly what we are trying to DO with this directory, at the moment my task, as stated, is to recreate the staff directory as it currently is, only in a format where it can be updated and maintained in a reasonable fashion.

The total list of data to go into the reports SOMEWHERE includes:

Employee name

Employee department (and subdepartment depending on which part of the directory you look at)

Employee position (job title)

Employee status (Staff, Faculty, Temp, Part Time)

Employee phone number

Employee email

Employee office room number

This list of department info includes:

Department name (and subdepartment name)

Department email (if there is one)

Department office number (if there is a central office)

Department primary phone number

Department secondary phone number

Department FAX number

And I have a "placeholder" field in the employee data table which I hope to be able to use on the directory page where the staff are listed in order of Department Head at the top, senior staff after that, junior staff after THAT. (This is a hard thing to sort on when you don't even have an official org chart...)

As I said above, the part of the directory that's foxing me is the one where she's got people listed by department AND subdepartment, with department info interlaced with the employee info. Ideally I'll be able to talk her out of wanting it arranged that way, but in the meantime I'm stuck trying to make it happen. I believe Access has the capability but I've only ever worked with simple database designs before and I'm frankly in over my head with this.

Thanks!

TwinOwl

Share via

Database design: Setting up a staff list with departments and subdepartments

3 answers