Can I Eliminate This Redundancy?!

[Jade74]

Hello guys & gals on here.

I've been struggeling with this problem for a couple of days now and finally decided to post here and kindly ask for assistance. I've got a pretty strong OO background, but haven't worked with databases much...

I'm trying to set up a small client database for my company. The client companies have staff members (StaffClients). Client staff can attend Events and work on Projects, which I've managed via junction tables. Our own Staff can also attend Events and work on Projects.

I've uploaded my first draft of tables and relationships here:
http://img514.imageshack.us/img514/3937/snapjl9.jpg
(sorry for the blurred bits)

Now I think I've done a fairly good job in normalising the tables, but one thing that I keep stumbling across is that I need two almost identical tables: one for our Staff and one for StaffClients.

They are subtly different, e.g. StaffClients have some extra attributes, such as JobTitle, Department and one StaffClient can be the assistant of another StaffClient (also done via a junction table). The other difference is that client staff can have different roles in a project (MainContact or Assistant) compared to our own staff (Manager or TeamMember).

As a result of these differences I have set up separate junction tables for both Staff and StaffClients, but are obviously very similar.

My question is: Is that structure sound? Or can I simplify it somehow before setting up the forms for the data entry?

Your input is kindly appreciated!!

 

[CraigDolphin]

Why not create one 'staff' table with all fields and an additional 'Employer' field (or something similar). Then have a 'Company' table that includes your company, as well as all your 'client' companies. Then, roles can be made available in combo boxes depending on whether the 'staff' member is employed by your company or not. 'Client' companies are all companies other than your own.

As for the extra fields being tracked for client staff vs your staff, I think you could either leave them null for records of your staff, or better yet, input a value like 'Not being tracked' or whatever. The latter better reflects the reality since those attributes could be tracked for your staff also (who knows, maybe it will evolve to do so someday) and avoids the use of nulls and thereby a compulsory slap with a wet fish from Doc_Man

As for the assistant issue, just include a field to store the pk (from your staff table) for the supervisor (stored in the assistant's record). This assumes one staffer can be an assistent to only one supervisor. If you have a many supervisor's situation, you need a new table for that.

This approach should help you simplify your data model considerably.

HTH

 

[Jade74]

Fantastic, thanks CraigDolphin! I've thought along those lines, but your post gives me the confidence to go ahead with this approach.

The only "problem" I see is that staff members of my company can take on *different* roles in a project than staff members of clients. So I guess I'll have to restrict that somehow in the input mask...?

 

[John_W]

Well I'm not so sure about this...

If the Clients Staff play a different role in your business model than your own staff, then they are different logical entities. Don't let the fact that they both comprise people fool you into thinking they are the same entity. If they're logically different, keep the tables separate.

 

[CraigDolphin]

Jade74: The other difference is that client staff can have different roles in a project (MainContact or Assistant) compared to our own staff (Manager or TeamMember).

JohnW: If the Clients Staff play a different role in your business model than your own staff, then they are different logical entities

I disagree JohnW. The issue here is that Jade74 wants to assign different roles in projects to staffers, depending on whether they are his staffers, or a client's staffer. This does not require that a client staff table be separate from a non-client staff table. Instead, what this calls for is a separate table to track roles versus staff and projects.

Thus:
tblRoles
RoleID(Autonumber; PK)
RoleName (Text)
RoleType (Text: Values allowed "OurStaff" or "ClientStaff"

tblStaffProjectRoles
StaffRoleID (Autonumber; PK)
StaffID (FK)
ProjectID (FK)
RoleID (FK)

Then, in the combo box that assigns the RoleID in tblStaffProjectRoles, you lookup the Employer of the selected StaffID, if it is Jade74's company then show only those roles with a RoleType = "OurStaff", if Employer <> Jade74's company then show only those Roles with RoleType="ClientStaff".

Addendum:
In case you need more info on how to do that, Jade74, have a look at the attached example for some ideas. If you need more specific help on the topic then search away and feel free to post back. And please note, the tables in the example are not fully normalized as yet since there are repeating groups in a couple of fields (such as StaffPosition, and even RoleType (although you could easily substitute values like 1 or 2 instead of "OurStaff" or "ClientStaff" in that field but I left it this way for clarity). You would need to create a table for staff position titles and save the StaffPosition key in your Staff table rather than the entire name (as you did in your original structure).

 

[Jade74]

Wow, thank you so much CraigDolphin! Your solution with the different RoleTypes is *exactly* what I was looking for! It was doing my head in. Even with an example DB! I am literally blown away.

Thank you (and also John_W) so much for your help, time and consideration. I really really appreciate it!

 

[The_Doc_Man]

And for the record, I rarely apply the "slap with a wet fish" punishment. Waste of good fish. Much better to panne' or bake with lemon-butter sauce.

Jade, admittedly you have a complicated situation. However, Craig's solution should very adequately address your issues.

The concept of having different "classes" of people often hides the fact that these people can do the same things - take on roles in a project. The fact that their employer might govern WHICH roles is not grounds to split the tables. It is grounds to have a way to know which roles apply. The difference between having certain fields that are often filled with "Not applicable" or "not tracked" vs. having a separate supplemental data table is often a matter of style or preference. Either one works. Both have pitfalls.

John W., your statement is correct - and incorrect - at the same time.

If the entities were totally different, fine. Split the tables. But the entities (in this case, the people) do similar things. They work on projects, fill roles, and attend events. The differences are in the nature of the roles, not the fact of filling them. So your abstraction was a bit too extreme. It was an incorrect normalization that should have been handled with a filter on applicable roles, not on a split of people types.

 

[Jade74]

Thanks for the elaboration Doc_Man. Your post was very educational and nicely outlined the core issues I was facing. I think just through this thread I have a much better understanding on how I can translate my OO knowledge into the world of databases.

I can't wait to give it all a whirl tomorrow. I'm actually looking forward to work! What a novelty

Thanks again, guys! Your help is kindly appreciated.

 

[CraigDolphin]

Glad to help. Now, if I can just get that lemon-butter sauce recipe I'll be a happy man

 

[The_Doc_Man]

Craig, I'll admit I'm spoiled here in South Louisiana with all the really good fish recipies we have native to the area. Had some fresh-caught (never frozen) redfish (red sea trout) last week when my step-son went fishing. Wifey did the lemon, butter, garlic version. YUM!

Go to the www.foodnetwork.com site and do a search for some of Emeril's fish recipes. Also, I've seen Giada De Laurentis and Rachel Ray treat fish this way. Emeril will of course try to "kick it up a notch" with his peppery "essence" powder (BAM!), while Rachel and Giada will probably take a more traditional garlic, lemon, and butter sauce with less pepper and more butter.

{DISCLAIMER} I take no responsibility for anyone using any of those recipies and clogging up their arteries because of it. {/DISCLAIMER}

Jade, no problem. Glad to offer what little I can add to a discussion. Since you were confused by the distinction, it tells me you are relatively new to databases. I didn't need your statement to that effect in your first post of this thread to know it.

The HARDEST PART of database setup is the design phase because it so DRASTICALLY affects everything else you do later. DO NOT be afraid to take extra time to assure that you really understand your problem AND your chosen way of representing your data. If either facet is still unclear in your head, you are not ready to proceed to the next step.