SQL design - consequences of creating multiple instances of the same staff member with different attributes

Question

I need to store information for staff. Each database instance is per parent company with multiple outlets underneath. Some of the staff that work at an outlet can potentially also work at other outlets, however as each outlet is for the most part autonomous, each outlet will not want other outlets to see their staff list.

I wanted to create unique staff instances and just relate them to the outlets that employ them, keeping their details uniform across the database. However my colleague wishes to allow each outlet to create their own staff members. The consequence of this approach is that John Smith might be a staff member at outlet A, Jonathan smith at outlet B and J smith at outlet C (as each outlet could enter pretty much whatever they want). Also each staff member has a set of skills and services associated with them, which also will not be uniform between outlets.

Will this approach cause problems down the line? At the outlet level it probably won't make any difference, but I am concerned that if the parent group ask for reports, the results may be misleading as perhaps 5 staff members might be returned, which in reality are the same person, however may have different details.

Answer 1

In addition to the helpful answers (that actually answer the question), here is some useful info:

If these people are staff members (i.e. they have jobs), then why not use their social security number/national insurance number as the unique identifier? That's guaranteed to be unique and they are each guaranteed to have one.

EDIT: US Social Security numbers are guaranteed to be unique and are not reused. See Q20 here: http://www.ssa.gov/history/hfaq.html

EDIT: No they're not! D'oh! Thanks to Mitch Wheat for this link: http://www.dailyfinance.com/2010/08/12/your-social-security-number-may-not-be-unique-to-you/ I guess the task is an impossible one then as there is no real way of solving the problem...

Answer 2

What you are describing, if I understand you correctly, is choosing between the prospect of denormalizing a person record per outlet (giving each outlet a completely independent copy of John Smith) vs. defining a single John Smith and then defining related tables that could belong to the outlet level of access.

If you have a choice (the freedom to design the system either way) the normalized way with only 1 John Smith + auxiliary tables with outlet-specific details when necessary is the correct way. I hesitate to say 'correct', but in the absence of very large numbers of users I would say denormalization here would only lead to avoidable integrity errors.

If you choose to denormalize now and not relate John Smith at outlet A to John Smith at outlet B even though they are in reality the same person you are both opening the door to illogical data (updates to one John and not both) and losing the ability to do simple things like count the distinct number of people in the database.

Failing to identify unique people now will prevent you from being able to properly relate other entities to a person in the future. This will complicate your queries at the very least and give rise to logically incorrect but technically correct information in many cases.

Answer 3

If outlets can't know the stafflist of other outlets, and outlets can add staff, there doesn't seem to be any way of preventing 2 different outlets from adding the same person, but their being recorded as different people in your system. So unless there is some central clearinghouse, or a way for an outlet to see if a person is already listed as a staffmember (w/o getting any details about the outlet(s) she is associated with), I think you're stuck.