Help with SQL Table Structure

That's called a recursive loop, where supervisor (should be EmployeeId of supervisor) relates to an employee in another table.   Here a Supervisor value will need to relate to an Employee value in another row.

This way you can map ee's to supervisors without having to create multiple tables and a big honkin' treeview structure to map an org chart.

At the least you'll want two tables: (1) An employee table and (2) a department table.

The supervisors are also employees, so they too will go in the employee table.

You can add foreign keys from the Employee.Dept to the Dept.Dept columns, as well as from the Employee.Supervisor to the Employee.EmpNum fields. I did not do this in the example below.

Here is a working example using temp tables (minus the foreign keys, but those are easy to add):

DECLARE @Employee TABLE (
    EmpNum      int         NOT NULL PRIMARY KEY,
    EmpName     varchar(80) NOT NULL,
    Supervisor  int,
    Dept        int )

DECLARE @Dept TABLE (
    Dept        int         NOT NULL PRIMARY KEY,
    DeptName    varchar(80) NOT NULL )

INSERT INTO @Employee VALUES (1, 'Adam Ant', 6, 1010)
INSERT INTO @Employee VALUES (2, 'Bobby Brown', 6, 1010)
INSERT INTO @Employee VALUES (3, 'Chris Columbus', 7, 1010)
INSERT INTO @Employee VALUES (4, 'Dom Deluise', 7, 1020)
INSERT INTO @Employee VALUES (5, 'Eddie Edwards', 8, 1030)
INSERT INTO @Employee VALUES (6, 'Frank Furter', NULL, 1010)
INSERT INTO @Employee VALUES (7, 'Gary Gnu', NULL, 1020)
INSERT INTO @Employee VALUES (8, 'Harry Hamlin', NULL, 1030)

INSERT INTO @Dept VALUES (1010, 'Billing')
INSERT INTO @Dept VALUES (1020, 'Finance')
INSERT INTO @Dept VALUES (1030, 'Sales')

SELECT  e.EmpNum,
        e.EmpName,
        e.Dept,
        d.DeptName,
        e.Supervisor,
        s.EmpName   AS SupervisorName
FROM    @Employee e
JOIN    @Dept d ON d.Dept = e.Dept
LEFT JOIN @Employee s ON s.EmpNum = e.Supervisor

Select allOpen in new window

Sorry, I accidentally hit post while I was trying to Preview my table and wasn't finished typing up my question.

So, I was trying to figure out how to best set-up these tables in a normalized manner to avoid redundancy and discrepancies due to typos (same name spelled differently in two different places).

Department table: obviously only has one field (Department name)
Supervisor table: how many fields?  how to associate department with supervisors?
Employee table: how to link to other tables?

Then, I need to link all this in a query to get it to look like the original data structure I posted (s/b a query, not a table).

Thanks

Wow, you guys are fast!  Three comments before I even finished the question.
Looks I have some reading to do!

Thanks

Hi Joe. You won't need a supervisor table. Check my post above. The example is very simple, but you may want in the employee table other fields, such as first_name, last_name, address1, address2 (etc), city, state, zip, ssn, pay rate, etc.

Usually the supervisor would be in the same department, although that certainly is not hard and fast. I assumed that in the example above.

Thanks.  Lots of good advice here.  That recursive stuff is coming back to me now.  It has been a lot of years since I had to create a table like that.

I just need to go back to the people who use this and see how they would like this to work when they add new employees.
1.  Do they want choose the Supervisor and Department separately?
2.  Or, do they want to make a single selection which adds the Supervisor and Department at the same time?

For the second option, I guess I would need to first establish a Cross-Join table between Supervisor and Department to track all the actual combinations.

I will revisit this post next week once I find out what they want and have a chance to try to implement these changes.

Thanks!

I strongly prefer a separate "Supervisors" table (although I might prefer a different name).  This is not only more flexible, to allow multiple managerial links, but also allows data about that specific relationship to be added as well.  For example, I've worked where one person did most of the day-to-day management of the employee yet that person did not write that employee's year-end evaluation.

Also, keep in mind that not all "workers" are necessarily (direct) "employees" -- the most common typical exception is "contractors".

Supervisor tables are not needed. Typical ERP systems, and many home-grown systems, carry appropriate flags in their employee table, including whether salaried, exempt, contractor, etc. The data is far more common, since most supervisors are employees, as are most managers, directors, etc, on up the chain of command. No flexibility is lost, and additional management of another table is not needed.

In the real world it is unnecessary, a waste of additional management and over-delivery.

Supervisor tables are not needed.
Supervisor tables may not be needed is about the best you can say.

Much depends upon the situation and that data.
I can think of a whole whack of data that could be normalized out about a supervisory positions.
EmployeeID
Department
Seniority
Primary shift
Date promoted
Date demoted
Date supervisor training completed
Reports to

And on and on.
Sometimes the requirements of the role will outlive the employment period of the person filling it!
It's a normalization exercise -- it all depends on the data you've got, the situation you've got, and what you would like to record and capture.

A separate supervisor table is not part of most standard ERP and HR models. There are more supervisory positions than those just called "supervisor".

The properties you listed can apply to anyone in a position of authority, not just supervisors. However, they usually are still employees, nonetheless.

Bonuses, promotions, demotions, and all manner of activities on an employee can be tracked in their own child relationship table, but the position of an employee is usually a property of that employee, except in rarer cases where someone wears more than one hat. In those rarer cases, they usually carry the primary position designation.

This is not subjective, some preferable ideology or per some poster you prefer. This is the standard for many ERP models and HR modules.

The problem with having a separate supervisor table is that supervisors are supervised.  Does that mean you need a separate supervisor table for each level of corporate hierarchy.?

If each employee has one and only one supervisor -- and that will be the case for eternity

I think you've led a sheltered life:)  I once had the misfortune of working for a company that was experimenting with matrix management and so everyone seemed to report to everyone else.  Which brings up the possibility of employees being on loan to some other group.  If I had to model that, I would use a junction table that joined the Employee table to itself rather than embedding the supervisor in the employee record.

Thank you.

:)

I know of many folks who are in SAP and PeopleSoft hell.
And they are condemned to remain there, stuck on the Procrustean bed of pain

The joy of Access is that, if the situation warrants, you can build what you need.
the position of an employee is usually a property of that employee
No argument there
But the data you may want to gather about the job roles of a company may lend itself to many normalization schemas.
And utilization requirements, for that matter.
If the standard model is over/underkill, it may need usability adjustments.
And the properties and requirements of a supervisory role are usually properties of the role, and not of the employee filling it
 
This is not just subjective to an ideology or some poster you prefer
If I am the dev in charge it very well can be!
And that's my point.
You disagree, and think that there's an ironclad 'best-practices' setup for this kind of data.
And that's ok.
@JoeMiskey will have to figure out what works for his scenario.
Clearly, since someone started out with a denormalized structure in the first place, something needs to be looked at.

@PatHartman
I think you've led a sheltered life:)
I've dealt with Fortune 50 down to mom-and-pop and everything in between.  I've learned that very seldom does one size truly fit all.
The problem with having a separate supervisor table is that supervisors are supervised
Supervisors aren't supervised.  Employees are supervised (maybe.  Sometimes departments are supervised and there really isn't a direct relationship between employee and supervisor, only an indirect one through department.)  Supervisors happen to be employees whose IsSupervisor property is TRUE and that may be all that's needed in the employee table about that particular fact.
There's a bazillion ways to model it.
It's nice to model it the way the facts on the ground dictate it, because it can be subtly different for every firm.

Supervisors aren't supervised.

What?!?! That definitely is wrong and unrealistic. Except maybe in Washington DC.

Supervisors ARE supervised, held accountable and promoted, demoted or fired based on accountability.

In the standard company and corporate structure, everyone is supervised, including the CEO (by the board). Accountability is the make-or-break principle of any company.

You're arguing against reality, standard models and modules, including SAP, PeopleSoft, etc. SAP does not have a supervisor table in its HCM module, nor does PeopleSoft in its CRM/HR modules.

Curious the insistence. Pat is right. On numerous counts. :)

You missed it :)
Go back and read it again.
everyone is supervised
Generally, yes.
But a row In tblSupervisors doesn't need to have ANYTHING about supervisors.  It's the rows in tblEmpolyees than need to get direct or indirect relationships to tblSupervisors.
But HOW one is supervised can vary greatly.
You may report to a QA supervisor, an hours-of-work-and-pay supervisor, a purchasing supervisor, a shift supervisor and a department supervisor all at the same time.  I know I have.
I worked at an 1100 person firm where ~40 could skid you.
Usually it was the hours-of-work-and-pay supervisor who lowered the boom, but it could be any of them if you mouthed off the wrong guy at the wrong time.
That would be VERY hard to model with only an employees table.
In fact, it would be extremely cumbersome to try to get a listing of supervisors and who they could fire through such a model.  A department and seniority model would make that task in that situation a piece of cake.

And in the that model, when a supervisor was added to a department -- well he gained the right to skid anyone on his watch.  It would have been far more cumbersome to add/remove that based strictly on employees.

Y'all have heard of a little something called Active Directory right?
Where you manage individual users by their membership in groups, and grant rights and properties to the groups and not the users, right?

It's really not that alien of a concept.

Digression and superfluity. This is about a (very much still) unnecessary additional table for supervisors. Period.

Definition - What does Normalization mean?
Normalization is the process of reorganizing data in a database so that it meets two basic requirements: (1) There is no redundancy of data (all data is stored in only one place), and (2) data dependencies are logical (all related data items are stored together).

http://www.techopedia.com/definition/1221/normalization

If there's nothing unique about supervision and there is nothing redundant about the data stored in the various tables that keys upon something else, well then there will be absolutely no need of any additional tables.  But there will be a need, almost certainly.
That's normalization.

See my previous post.

So like I said in my first comment, if they could have more than one supervisor, then you'd add a linking (or junction) table.

The only way I'd have a separate supervisors table is if it described the position (role) of a supervisor, but it would still have a FK back to the Employee table and a junction table in between if one employee could fill more than one supervisory role.

Jim.

Departments table (did, dname, ....)
Employees table (eid, ename, reports_to_eid, belongs_to_did, ...)

Is your issue more complicated that can't be handled with such two tables?

You may rephrase the question after summarizing and making use of this debate.

This is about a (very much still) unnecessary additional table for supervisors. Period.

Wrong.  Any relationship that has data about it that needs to be stored needs its own data store for that data. IF such data exists, some type of structure is required.

Again, as I noted earlier, I would not necessarily call it a "Supervisors" table, although I wouldn't either necessarily quibble with those who did.

But multiple supervisors is unquestionably a real possibility.  It's just bizarre to claim otherwise.  For example:
"
According to Robert Sutton, a professor of management science and engineering at Stanford University and the author of Good Boss, Bad Boss, it is extremely common these days to have more than one boss. “As you to go to a matrixed structure, you can easily have between one and seven immediate supervisors,” he says.
Adam Grant, an associate professor at the University of Pennsylvania’s Wharton School and co-author of “The Hidden Advantages of Quiet Bosses,” concurs. “As companies continue to flatten, organize work around specific projects, and use temporary teams to complete projects, many employees find themselves reporting to multiple bosses,” he says.
"

International Paper had several such situation while I worked there, including "indirect" and "direct" supervisor lines. [Yes, it was almost as confusing as it sounds.]

According to just about every ERP and classic HR model out there, there are no supervisor tables. Employees (whether non-exempt or exempt) are in employee tables. Period. This is argumentation for argumentation sake, without regard for the reality of business or efficient design. More than one boss and employee relationship, whether flat or fuzzy, can be handled by a junction table to the very same employee table.

As for Prof Sutten, there are those who do, and there are those who teach. Most of real-life design comes from those who do. :)

Interesting. I worked at International Paper, too. Short contract.

I wish I had the time available to review "just about ERP and classic HR model" in the world!

1) So you're claiming that if someone has multiple supervisors, an additional table is still not required?  
2) Or that no company on earth actually has multiple supervisors for a single employee?  

I'm not sure I'm following the reasoning that allows you to so broadly dismiss everything everyone else is saying.  I mean, just because you ignore the points some of us have made doesn't mean those points don't exist.

Also, as nick67 also alluded to, if data specific to the relationship between an employee and his/her supervisor exists, such "intersection data" must, by the rules of normalization, be moved to a different table, because such data does not depend on "the key, the whole key, and nothing but the key" of the employee table.

Wow, didn't realize my question would stir up such a debate!

Just to clarify my situation/my needs:

1.  Each employee will be listed exactly once
2.  Each employee will be associated with exactly one supervisor and one department
3.  Each supervisor may be the supervisor of more than one department
4.  Each department may have more than one supervisor
5.  I do not need to track any in-depth details, like dates of employees or supervisors

This is just a small part/detail of a larger database.  The goal isn't really tracking employees and supervisors.  The main objective of the database to audit claims that we receive, and we are just trying to capture details (like who worked the claim, and who they report to, which is just one small part).

Since I do not need to worry about multiple levels of supervisor here, I think I should be able to get away with a single employee table (and not need a separate supervisor table).

I have gotten pulled away temporarily to work on some other things, but hope to find some time to work on this within the next few days.  I will report back.

Thanks

I guess it depends on one's point of view.

Are you supervising (just) a specific person(s) who currently happens to hold that job(s) now OR do you supervise a specific company position(s), whether the job is filled now or not?

Say EmployeeA supervises two people, EmpB and EmpC.  Assume both EmpB and EmpC suddenly quit.  How does one at that point in time know that EmpA supervises both those positions?  I mean, if that relationship is stored only for the reporting employees, and they no longer report, what then?  Another of the goals of modeling data is to make sure that data relationships don't "disappear".

@dsacker,
Perhaps we debate with you at cross-purposes.
From a DBA's viewpoint, normalization has some very clear methods and requirements.
Employees (whether non-exempt or exempt) are in employee tables. Period.
Of course.  Of Course!  OF COURSE!  Everyone drawing a paycheque is an employee and is in that table
can be handled by a junction table to the very same employee table.
That's an additional table, which we have been saying is going to be required in all but the simplest of scenarios -- that scenarios where tblEmployees can have a field SupervisorID which is in fact a self-join back to tblEmployees.
there are no supervisor tables
It seems the name fusses you.
Call it job titles, call it roles, call it responsibilities, call it whatever you want, there WILL BE data about employees that can be normalized out into additional tables.  And there WILL BE data about those employees who have supervisory duties that pertains solely and strictly to employees that share such duties.  And HOW to normalize the data will present options depending upon what an outfit wants to track.
I have noted one common situation -- where supervisors DO NOT supervise individual employees, but rather supervise departments.  In the DBA viewpoint of that scenario each employee has a department.  Each department has multiple supervisors.  There are described supervisors roles, and each employee is related to a supervisor indirectly through membership in a department.  This is EXACTLY the model used by Active Directory for the management of network rights.  The properties of the end user are described by the end users membership in various groups.  The properties of an employee are described by that EmployeeID being a foreign key in other tables.

You come across as insisting that an employees table alone, or perhaps and employees table with a junction table having nothing but employeeIDs in it, will be good and sufficient.  And if you were going to try to create a generalized system to model every possible setup, I suspect that -- despite how incredibly tortured the logic and the administrative headache would be -- that you would use such a model.

But then, it's not called PeopleSoft and SAP hell because it is nice, good, efficient and intuitive.

@JoeMiskey
There is the Steve Jobs adage 'People don't know what they want until you give it to them'  It would be better in the beginning to flange up an extensible system that when someone says 'hey, I sure would like to be able to know...' that the data design underneath doesn't need to be changed, only extended (more fields add to tables, not new tables and relationships added.
That's my thought anyway.
The relationships between employees are complex, and can and do evolve over time.
It's nice if the tables of a database DO NOT need to follow suit because your foresight was sufficient to the task :)

Certainly it's Joe's call on whether to add an additional unneeded table or not. I've been in this business almost 40 years and have yet to see a supervisor table in any company, but I'm sure one can be conjured just to satisfy the ad-nauseum rhetoric.

Simply put, the employee table will suffice just fine, especially within the simplicity of Joe's definition (which pretty much was as expected, given this was an "Access" database and certainly didn't seem complicated to begin with).

I wish I had the time available to review "just about ERP and classic HR model" in the world!

Perhaps a little less time trolling for questions to dispute might yield the needed extra time. :)

All in all, Joe, it is your call. Hope we all helped in some way. And perhaps also, provided a chuckle or two. :)

Very well stated, Nick67!

However, I do have one refinement I would suggest (emphasis added by me):
>> 
there are no supervisor tables
 It seems the name fusses you.
 Call it job titles, call it roles, call it responsibilities, call it whatever you want, there WILL BE data about employees that can be normalized out into additional tables. <<

Just to clarify, >> that must be normalized into additional tables. <<

@ScottPletcher
We are all aware of sad choices (sometime that we made ourselves!) not to completely normalize data.
It makes life hellacious afterward, and you usually regret it, but the db will still function.

1.  Each employee will be listed exactly once
 2.  Each employee will be associated with exactly one supervisor and one department
 3.  Each supervisor may be the supervisor of more than one department
 4.  Each department may have more than one supervisor

1.  That suggests an employees table with all its goodies, and that has a foreign key to...
2.  A department table that describes whatever you want to say about a department.  But...
3. and 4.  Multiple supervisors means you'll need a table holding both DepartmentID and EmployeeID as foreign keys to model this.

On the other hand Each employee will be associated with exactly one supervisor and one department is a bit of a conundrum.  Departments can have multiple supervisors and supervisors can be responsible for multiple departments, but each employee has only a single supervisor.  That's a logical swamp.  Presumably, there should be some sort of constraint that you and your supervisor share a department, but with departments having multiple supervisors, at best you'd get a combobox with multiple possibilities to choose from.  The person supervising multiple departments -- what department is he/she a member of?  Lots of fun!

I don't see the downside to creating a separate table.  There's no harm, period, even if that table forever remains a simple 1-1 relationship with Employee.  But, if additional requirements related to supervisor details become needed, the supporting structure is already in place and existing code doesn't have to be reworked.

Int'l Paper bought a lot of companies.  IT integration would have been so much easier if those companies had used some foresight in design and not been so purblind to anything and everything they didn't happen to be doing at the moment.  No, you can't go crazy, all SAP, on everything.  But a very simple design mod that allows future expansion at no cost today won't hurt -- in fact, the developer that had the foresight to pre-plan for it might get some recognition for the skill in perceiving a potential future need :-) .

Amazing that Int'l Paper even spun up each morning without listening to such DBA wisdom. :)

Supervisors are, generally, employees. Same needs for addresses, SSN's, pay rates, etc., etc. An employee can have another employee as its supervisor. That supervisor employee can have another manager as its employee. Repeat ad nauseum.

Multi-employee relationships can junction back to the employee table.

Certainly a department table would be separate. And in a many-employee to many-department relationship, a junction can accommodate that, too.

But despite the grandiloquence, it's amazing how the usage of an employee table with no supervisor table has worked flawlessly in real life for decades with little or no maintenance and has accommodated the business changes throughout that time.

It's also amazing that when two supervisors who supervise two different departments, one with 78 employees and the other with 62 switch positions, that you'd have to update at least 140 rows instead of two.  If the software package you work with requires this, it can get done.  If you are the poor HR person that has to walk through forms to update the 140 rows, though and make sure you committed no errors in doing so, you definitely curse the PHB that foisted such a stupid system on you.

But hey, it worked flawlessly.  Somebody spent a couple hours punching through the updates that good design could have gotten done in a couple seconds.

And then there's the fun things, like nice systems that constrain it so that the supervisor has to be a member of the department he supervises.  So the first supervisor can't be added to the required records until he's in the new department -- but he can't leave his old department until he no longer supervises anyone in it--because that's constrained.  So those records have to be pushed to a different supervisor temporarily, because everyone HAS to have a supervisor -- oh, wait!  There is only one supervisor in that department, so lets create a dummy employeeID to be the 'in transition' supervisor while we get this done.  And then, instead of 140 updates, we can maybe go for 280, and the inevitable human errors creep in and we have a mess -- maybe on paycheque day, because that's always fun for everyone.

It's hell.  And the end users curse it.  And they have for many years, and will for many years to come.

In a world of hypotheses and DBA's standing around in their own little world surmising, must be worse than hell. For them. Users have used it well without much ado almost as much as the rhetoric expelled by island-DBA circles.

Expecting more verbiage shortly. Sounds like old times in the IT shop. :)

Nah :)
It's not heaven, but it is freedom.
Normalization is the critical skill that no amount of 'look-ma-no-code' and multi-value field nonsense -- or CodeFirst web design -- that MS pushes out that cannot be dispensed with.  The ability to look at the business rules and raw data and fashion solid, extensible, normalized data designs.  Not everyone and their dog can do it, and while it is a skill that can be learned -- like thinking about set logic instead of loop logic -- it really does demand that you set pre-conceived notions aside.

But then, you know that.
This ain't your first rodeo.
I think we've all cleaned up a bad data design a time or two.
The thing about being an Access dev is that it is ALL wide open.
You are free to build the system that works for the situation.
I work oilfield.
This is a common conversation

Listen, you will have to have a PO for that job
Ok
But before I can give you a PO. I have to have your invoice number and the exact total
Ok
And the PO Number has to appear on the invoice
Ok
<incredulous>You can do that!?!</incredulous>
Sure, you need it, so we've got a system built that'll accommodate that
Man, I wish we could do that.  It's hell on wheels when we run to someone with a system like ours.  You can't edit POs and you can re-submit invoices or reprint them.  It's Catch-22 neither one of us can give the other what we need to do a transaction!
<grin>That's why we DON'T have a system like yours -- so we can make it easy for you to deal with us</grin>
Well, thanks.  you don't know how good you have it
We do.  Trust me.  We do!

I know of a lot of unhappy people trapped in IT systems that are supposed to 'help them and make them more productive.'

I've worked oilfield, manufacturing, service industries, big, mid-sized and little companies, contracting on my own since 1997. I think we all have been around the block. In a room with each other, we'd probably all be laughing at the caricatures exemplified in these discussions. It has brought a chuckle.

The IT business has been the most rewarding profession I could have chosen. I've seen many users very satisfied (and yes, I've spoiled a few) with simple to elegant solutions, and this has been a good profession to me. But it meant not letting brain cells rot and keeping up with the fast-paced changes in this industry. As we probably all can relate, I've forgotten far more extinct technologies than I can remember. :)

Most importantly, though, is that this is Joe's quest for a solution. The main core of his needs are in the first 5-6 posts, post #40803261 really offering the optimal solution. :) :)

dsacker:

But you are the one that just keeps spouting off without addressing the actual data situations I mentioned.

How do you handle a supervisor who temporarily supervises no one?  Or a supervisor of typically 5 employees who now only manages 4 because one has recently quit?  How do you produce a valid org chart without, in your design -- the only possible valid design according to you -- an employee to indicate that the supervisor has a 5th job under him/her?

Whatever you've done in the past, it's clear that data modeling was not one of those things; at least not on any significant level.

I don't handle or make org charts for your situations, nor do I need or desire to substantiate to your errant surmising my expertise or what I have done. I very successfully and to their satisfaction handle real needs from real users, i.e., the author of this question. Consistently I solve them without the superfluity of demands for answers to off-tangent, bloated and baiting questions from others, especially island-DBA's. Ergo, you'll just have to live without my catering to your requests, since it's not about you, but about the author's question.

When the author requests a solution for a temporary supervisor, I'll help him with one. The simple design accommodates such, but that may be too easy for a complex DBA alternative and ego.

This is not a cop-out, since you are injecting rather than supplying simple needs for a user. This is wisdom to refuse subjective complexity. I've refused many like you, much to the benefit of the end-user. Will again. Will here.

Well stated. Rubber that meets the road.

Like I said, the purpose of this database is not to track the shifting dynamic between employees and supervisors.  It is to track claim audit requests.  I think the part of this they are most interested in is tracking the employee.  I was just trying to try to figure out a way to reduce the redundancy (and typos they have).

On further inspection, it is really quite interesting how they set this up.  So they have a table with these three fields, but then on the audit entry form, they have three distinct fields with combo boxes, each a SQL Query off of that table, but only returning the one column.  So regardless of which employees they linked to which supervisors or department, they can pick any of the values for each of the three combo boxes!  Not only that, they didn't even bother to Group or user "Select Distinct", so the Supervisor name appears multiple times in the Combo Box (the same is true for the Department Combo Box as well).

So, I think either way I choose to go is going to be a bit improvement over what they have now.  Just as a quick fix before I can set up my tables and make the changes, I added "Groupings" in those Combo Boxes, and they thought it was the greatest thing that each Supervisor now isn't listed 10-20 times in the Combo Box!

How do you handle a supervisor who temporarily supervises no one?  
A left join that returns no supervised employees would find those people.
Or a supervisor of typically 5 employees who now only manages 4 because one has recently quit?  
Including Termination date in the selection would allow you to bypass terminated employees
How do you produce a valid org chart without, in your design -- the only possible valid design according to you -- an employee to indicate that the supervisor has a 5th job under him/her?
This is a different problem and would require storing an authorized employees count somewhere.  If you had this need, you would probably create several new tables to manage the org chart because you probably won't want just an empty box, you will also want a job title.  So in this case, the employee record would probably link to the org chart.  That table would include an authorized count and a job title.  Printing the org chart would left join the org table to the employee table so we still haven't found a need for a "supervisors" table.

No table name should begin with "tbl" or any other prefix.  That's obsolete and quickly becomes inaccurate (when a table must become a view for whatever reason).

As to the current task in hand, you must keep in modeling that you are modeling the data relationships, not the people currently in those roles.  The differences can be subtle, but they are there.

>> 1.  Each employee will be listed exactly once <<
Perhaps that should be "Each job/position will be listed once".  It's very possible for a single employee to fill multiple roles temporarily after another employee resigns, and to report to different supervisors for each of those roles.

>> 2.  Each employee will be associated with exactly one supervisor and one department <<
Again, what about temporary absences of other employees?

>> 3.  Each supervisor may be the supervisor of more than one department <<
And knowledge of that set up must continue to exist even if the current supervisor takes a different job within the company.  The next supervisor must know which departments he/she will be responsible for.

>> 5.  I do not need to track any in-depth details, like dates of employees or supervisors. <<
I'd verify that if I were you.  Make sure that is truly the case; otherwise, you might find yourself trying to reconstruct that data after the fact, which will be much more difficult.  Keep in mind that employee lawsuits are but one possibility that might require more details about the situation at particular points in time.

I fear that this thread seems to be getting out of hand and taken on a life of its own.  It wasn't my intention to stir up a hornet's nest here.

As I said in my last response:

Like I said, the purpose of this database is not to track the shifting dynamic between employees and supervisors.  It is to track claim audit requests.  I think the part of this they are most interested in is tracking the employee.  I was just trying to try to figure out a way to reduce the redundancy (and typos they have).

I think I have enough information here now to try to do what I need.  Like I said, I will post back here once I have the chance to try to implement something.  But due to the strong differences of opinion, it appears that regardless of whichever way I go, someone is bound to think that I made the wrong decision.

:)
Careful @JoeMiskey <grin>
Once they see what can be done, the request will morph from 'what we asked for' to 'what we meant.'
It's always amazing how a well-constructed database app subsumes more and more functions into.
It's equally amzing about how stupid MS is when they are repeatedly told that well-designed Access apps grow in functionality over time to the point they outgrow Access -- and we still have no good solution for moving the UI and logic we build to a larger scale platform.

And as for debate -- well, that's a healthy thing.  That's why you bring a lot of sharp minds with varying experience to the table.  It exposes the 'unknown unknowns' as it were.

@PatHartman
Each supervisor may be the supervisor of more than one department and Each department may have more than one supervisor
Taken together, they mean that somewhere along the line, there'll be a table with an AutoNumber, a DeptID and an EmployeeID and for each department/supervisor pairing there will be one row.  That was all I meant.  Joe says each employee has only a single supervisor (at the moment!)  That means you could have a SupervisorID in tblEmployees that is a self-join back to tblEmployees.
I wouldn't architect it that way because, over time, that is almost certainly going to break if the firm grows.
And I don't think any of us disagree that the employees table will likely NOT be the best place to capture the data about supervision.  It's the shape of the ancillary tables and their naming that are matters of debate.

No table name should begin with "tbl" or any other prefix
<grin>Do we really want to blow the debate sky-high?!?  I leave that one alone </grin>
Use a naming scheme that is appropriate, informative, rigorous and concise for the context!

But it appears that regardless of whichever way I go, something will think it is the wrong way to go about it.
Nah! "Did it work?"  That's the criteria!
The missus has standing orders that my epitaph is to read 'It wasn't pretty...but it worked.'
Like any good epitaph, it has layers of meaning :)

So, I have been working away at this for the past few hours, really digging deep into the data, and made a few rather unpleasant discoveries -- they have a few records (a very small percentage) that change the assumptions I thought I was working under, namely:
1. the same employee is listed twice, with different supervisors
2. a few supervisors are also listed as employees with other people as their supervisors

So, much to my chagrin, it appears that we have many-to-many relationships all over the place:
- between Employees and Supervisors
- between Supervisors and Departments
and we have different supervisor levels.

So, I am thinking of proceeding this way, with three tables after all:

Department Table
- ID field
- DepartmentName field

Supervisor Table
- ID field
- SupervisorName field
- DepartmentID field (from Department Table)

Employee Table
- ID field
- EmployeeName field
- SupervisorID field (from Supervisor Table)

Does this seem like a reasonable approach?

Regarding dsacker's response...

OK.  I have worked with (and designed) many Access and SQL databases over the years, but I guess I have been pretty sheltered in that I hardly ever had a situation where I had to use Junction tables.  I am just trying to figure out how to best structure data entry forms using them.

So, our staff wants to be able to open a Form where they can enter a a new employee, their department, and their supervisor.  So that would obviously involve all three Employee tables you have outlined above.  What is the best way to build/structure this entry form when there are junction tables involved?  Using Subforms?

Thanks

There's been a lot of verbiage already.
Supervisors are also employees, so the 'supervisor' (job role might be better) table can probably have a foreign key to the employee table, rather than a SupervisorName field.  After all, if a supervisor marries/divorces/changes name, the employee table is a single spot to hold that data and change it without duplication.

I think part of the sound-and-fury was different viewpoints on what data item has a property of supervision.
The simple case is that employees are supervised and therefore the identity of the supervisor is a property of the employee object.  I think that is rarely the case, though.  Supervision is a property of a job role.  A job role is defined, and certain groups of people, processes or outcomes are to be supervised by that job role.  A job role may be filled by a single employee, or perhaps multiple people have the same job role.  And perhaps a single person fulfills multiple job roles (you've said that a single person supervises more than one department)

So the first thing to determine is the nature of supervision.  Do supervisors oversee departments, or groups of people, or a number of individuals or a mishmash of all of the above?  You'd like to avoid a design that requires you to change many, many records if supervisors switch job roles.  If on the other, supervisors really do supervise a number of individuals, then there's no hope of that!

So, our staff wants to be able to open a Form where they can enter a a new employee, their department, and their supervisor.  So that would obviously involve all three Employee tables you have outlined above.  What is the best way to build/structure this entry form when there are junction tables involved?  Using Subforms?

Assuming the department is already defined, your form will (of course) have all the pertinent info for the new employee:

Name,
Age,
SSN,
Address,R
Rate,
Type (i.e. supervisor, manager, director, NULL for non-authority position)
DepartmentID (on the form, but not in the Employee table)
SupervisorID (on the form, but not in the Employee table)

From there, when you go to save, you won't save the DepartmentID within the Employee table. Rather you'll save it within the EmployeeDepartment junction table.

INSERT INTO EmployeeDepartment (EmployeeID, DepartmentID) VALUES(form.EmployeeID, form.DepartmentID)

Obviously, I'm pseudo-coding. You can add an "IF NOT EXISTS" in front to make sure you don't add him twice.

That establishes one of many possible employee-department relationships.

Next, you'll add a record to the EmployeeEmployee junction table:

INSERT INTO EmployeeEmployee (EmployeeID, SupervisorID) VALUES(form.EmployeeID, form.SupervisorID)

(Again, pseudo-coding.)

This will establish the relationship of the employee to the supervisor (who is already in the Employee table).

Since that supervisor is already an Employee, he will already have an employee-department relationship of his own. You buy a lot that way.

Nothing to be shocked, shocked about your revelations. That's the awesomeness of data mining, and it sounds like you're doing an excellent job flushing out the fine-tuned requirements.

One correction: Five posts up (post ID: 40808944) I put the SupervisorID in the Employee table. It doesn't go there. Rather it goes in the EmployeeEmployee junction table, which then can join multiple employees to multiple supervisors (in the same employee table).

OK.  Just to recap:

We have a SQL Back-End and Access Front-End.
All of the Access data entry Forms are bound Forms to Tables/Queries.

My biggest concern at this point is creating a data entry form for adding new members.  In the type of scenarios mentioned here (with the use of Junction tables or other additional tables), is there any way to create a Bound entry form?  Or will we need to use an Unbound form with VBA code that runs SQL code to update/add records?

In researching how this database works, the design is really quite peculiar.  The have this one de-normalized table, so that each record has Employee, Supervisor, and Department, but then when they are entering the new audit records, they have separate combo boxes for all three fields on the entry form.  So they are making three separate selections (meaning that they could conceivably pick a Employee-Supervisor-Department combination that doesn't even exist in their table!  

It seems that the only way these fields interact at all on the entry form is that they are limited which Departments to show (i.e. there are different entry forms for different lines of business, so they limit the departments for each one).  

So now you know a little bit more of the mess I am dealing with...

The goal (at least at this point) is not to rewrite the entire database (though it could certainly use it).  We don't have the resources to do that right now.  I was just hoping to do some quick database normalization for them to help avoid errors, and not spend too much time on it.

I will take a fresh look at this again tomorrow morning when my mind isn't so jumbled!

Thanks

Hi Joe,

Here's a toy to play with.
A blank sheet is always a bugger.
Transmogrifying something else, not nearly so hard.
Hopefully it gives you ideas

Nick67
Miskey.mdb

OK, I copied all the responses out to Word, and removed the stuff that wasn't relevant (and go it down to 20 pages!), and re-read through it all.  Lots of good points made by people.

I think I am going to try to go with the model laid out here: https://www.experts-exchange.com/questions/28682227/Help-with-SQL-Table-Structure.html?anchorAnswerId=40808944#a40808944 (plus the correction he made a few posts later saying that the SupervisorID field would not go in the Employee table, as that would be found in the junction table).  It seems to make the most logical sense to me and my situation.

Setting up those tables will be pretty easy.  My question is this.  Remembering that all these tables will reside in SQL, do I need to do anything special, i.e. something to designate them as "foreign keys" in SQL, or establish relationships (like is done in Access)?

Then I will just need to figure out how to build an updateable Access form that links all this stuff together (and still allows for record updates and new record additions).  I might be able to borrow some of the concepts Nick used in the database sample posted (I figured it would probably involve the use of Subforms).

Thanks

You can hook up foreign keys either by your own SQL commands.

Or (and you might like this), you might want to design your tables in the Database Diagrams. You can create a new diagram, define (or bring in already created tables), and you can draw lines from a field in your table to another field in another table, automatically (almost) creating the foreign key relationships.

If anything, it's fun, and you'll learn a lot. You can even copy to the clipboard the foreign key SQL, so that you can see what it's doing.

SQL Server has the same concept of relationship as Access does and SSMS has the tools necessary to set those relationships up.  They aren't nearly what Access offers and guys who beat SQL Server around the ears don't use them because the naming conventions that they use for the newly created indexes and constraints are just gross.  People who are way better with SQL than me write and execute T-SQL to get 'er dun.

My toy showed how to set things up so that if Frank Furter and Harry Hamlin switch positions that updates to just two records in tblJobRoles gets things done.  The business logic is captured in query definitions and VBA code, and no SQL is required for CRUD work.  I would put in the employee form some event code upon an update to an employee's department if his supervisor data would no longer be valid

When you create a structure such as a junction table that directly relates two EmployeeIDs, you will be looking at many. many record updates in the junction tables to accomplish the same thing.

I did give you everything you asked for:
Employees may have more than one supervisor
Employees supervisors must come from a pool of supervisors from their department
Supervisors may supervise more than one department
Supervisors have people they report to.
Enough data in each record that someone looking at the design later can tell why each table was required.
A form to allow for entry of a new employee and select of a supervisor on that form.
A setup that easily allows a query of 'who are my supervisors' and 'who do I supervise'
Easy extensibility and robustness for changes to future regimes

Access guys generally don't do many-to-many relationships because creating a nice UI for the end user is usually very unsatisfying.  And given that there really isn't such a thing as a many-to-many relationship (at the record level) you can in practice break a many-to-many relationship down to a number of one-to-many relationships (nobody should bash my teeth in for saying that because it is true from an academic standpoint.)  These are easier to understand, later, and more importantly every dev can create a good, editable UI for a one-to-many relationship (subform!)

You gotta do what's good for you.  My toy is useless without the data model behind it.
If dsacker's data model grabs you, then you have the task of creating a form for the (mass) editing of junction table records that your end-users know when to use and like using.  I've never built one I've found satisfactory. Perhaps @PatHartman may have ideas about how to knock that particular bit of UI into shape.

Another question.  I want to go back to that Nick said here: https://www.experts-exchange.com/questions/28682227/Help-with-SQL-Table-Structure.html?anchorAnswerId=40803312#a40803312

Currently, the database does NOT have an "ID" field for the Department table.  It is just one field, "Department".  I agree (and think most of us do too) that there should be an "ID" field added.

So, I added an ID field with SQL code, i.e.

ALTER Department
ADD Column ID int identity(1,1)

Select allOpen in new window

That worked just fine, adding an autonumber ID field to the table.

However, when I try to change the table properties so that the ID field is now the Primary Key field instead of the Department field, Microsoft SQL Server Management Studio is giving me the following warning:

The primary key or unique constraint cannot be changed until its existing relationships are deleted.
Do you want to delete the relationships?

This warning makes me a little nervous.  Will it cause any problems for my database if I do this?  Or will I need to perform some sort of clean-up after doing this?

Thanks

You might find it easier to reload your tables, first creating them via SQL with the IDENTITY as the primary key, then loading them using explicit field references:

CREATE TABLE Department (
    DeptID          int         NOT NULL IDENTITY(1, 1),
    DeptName        varchar(80) NOT NULL,
    --  etc,
    --  etc,
    LastField       varchar(80) )

ALTER TABLE Department
    ADD CONSTRAINT PK_Department
    PRIMARY KEY (DeptID)

INSERT INTO Department (DeptName, etc)
YOUR INSERT QUERY

Select allOpen in new window

That last statement above would be the pseudo-code to populate your table explicitly by naming the fields you want to populate and leaving out the DeptID.

You can load your primary tables first, then load your junction tables with all the employee-department combinations you currently have in Access. You can also load the Employee-Employee, aka their Superviser Employee ID, relationships, too.

If you want to then create some foreign keys, you might enjoy using the Database Diagrams that come with SQL Server.

You can even copy and paste the SQL to another query window to see the SQL code for creating foreign keys, if you're not used to doing that yourself via SQL.


It's pretty cool.

This warning makes me a little nervous.  Will it cause any problems for my database if I do this?  Or will I need to perform some sort of clean-up after doing this?

Someone has created relationship using the text-based field as a foreign key.
So you will need to look at all the tables where Department is used.
Those fields will be needing changes\updates.
The breaking of the relationship won't be the end of the world.
God knows I've seen enough software that doesn't even bother to do ANYTHING with the db

Then comes the finesse part.
You'll add DepartmentID into those tables than had Department in them
You'll update the value of DepartmentID so that it matches what department was
You'll create the requisite relationship
Then you'll remove Department from the tables.

But this will have repercussions in Access!
You'll have to see what objects and code were using the Department foreign keys (from tables other than tblDepartment) and expecting text.  New joins and variable types will come into play in some cases.

Changing relationships and PKs in existing data with a production app is not fun.
Which is why you got a lot of advice about making sure whatever data schema YOU choose won't need to be revisited.

My concern is that this is an existing SQL database that is already in production.  It has over 40,000 audit records in it already.  So I have to be very careful what I do to it, so as not to "break it".

They are very reluctant to bring it down, and even when they allow me to do so, I typically can only take it down for about a 2 hour window at a time.

If something happens, where it is down for an extended period of time (more than a few hours), maybe because something "breaks", they'll have my head!

As it is, once I make all these changes, I will need to go in and edit a whole bunch of existing entry forms where these fields are used.

Do you have a play database? Will they let you make one? Preferably a development database. There you could make all your changes, have a test form, and when ready, simply move your tables to production and port your form's connection back to that database.

I,e., a classic (hopefully somewhat informal) development shop.

Yes, I do.  And I think I know where you are going with this...
Yep, I should test all this out over there first.

I can't help but be reminded of something I read many years ago when I first delved into Access programming.
"In determining how long it will take to design a database, very carefully plan out how long you think it will take -- then multiply that number by 10!!!"

So much for a quick fix on this one!

When it comes to deployment, an ounce of pretension is worth a pound of manure. :)

Well then my son, I suspect what is before you is both easier and more complex.
In your shoes, I'd start a new Access mdb.
I'd hit the server and IMPORT all the requisite tables
I'd import all the Access objects.
I'd change every friggin thing that there is to change.
I'd fix and test, fix and test., until its production ready
I'd then get permission to use SQL Server Migration Assistant for Access to push the new backend to the server.
On a weekend, I'd migrate the existing data and give everyone the new front-end.

A bad data scheme is never easy to fix while in production.
Which is why a lot of questions we see about 'I can't get a form to do this' devolve back to 'what's your data scheme?  Have you fully normalized it?'

In your shoes, I'd start a new Access mdb.

Not an option, at least not for the back-end.  The back-end is already in SQL.  We spent a few months cleaning up the data, object/field names, and VBA code so we could use the Upsizing Wizard to move it to SQL.  Even with doing all that, there was still clean-up that needed to be done afterwards.  There is no moving the data back to Access now!

I'd change every friggin thing that there is to change.

We already had to go through something like this once when Upsizing.  What a nightmare that was!
The database has
- 9 tables
- 89 queries
- 12 forms
- 49 reports
so it was quite a bit of work (especially since their Main Menu has almost 60 command buttons on it, all with VBA code attached to them).

The thing about this, they haven't even really asked for this database normalization that I am trying to do.  I just noticed it as I was fixing up the database after upsizing, and it really bothered me, so I thought I would try to tidy it up a bit myself.

Basically, I have a one week window here where I can work on this before I get started on my next big project, and was hoping to sneak it in.  Looks like this may be way bigger than I anticipated, and I may need to "table" it until I have more time to devote to it, and test it thoroughly.

Upsizing or migrating?
They were two different things.
Upsizing used a wizard in Access and creates an ADP (now deprecated)
Migrating used SSMA.  Took me a couple days to straighten things around before I used it, and a couple test runs to ensure that things worked as expected.

There is no moving the data back to Access now
Not for production, but for data design changes and UI updates.
It is as easy as pie to pull all that SQL Server data back into Access as imported tables.
You then get to use all your familiar tools to get the PKs and data types and relationships right.
That really is easier to do in Access if that's your familiar tool.

You then make the UI work.
Given that you've sorted out the (I assume) SSMA snafus already, re-migrating should be far less painful.
And 9 tables!
Piffle. :)
160 tables
499 queries
170 forms and subforms
158 reports and subreports
50 code modules
112K lines of VBA code.
I wasn't kidding when I said be careful because well-designed Access apps begin to subsume everything around them :)

Nick67

160 tables
499 queries
170 forms and subforms
158 reports and subreports
50 code modules
112K lines of VBA code.

How long did it take you to clean that up?

That's real-time as of today.
It was around 1/4 to 1/3 of it's present size when I migrated.
I was always an autonumber supporter and I have only one table where I was dumb and used non-compliant field names.
So my tables didn't cause SSMA much grief.
And I knew better than to use hyperlink fields or anything else non-SQL server compliant
Queries threw me for a loop until I realized that while Access was going to push the query definitions to the server -- if I permitted it -- the Access mdb makes no use of them!
So there was no point in any agonizing about the queries
There were just tables to push up.
On 1-Mar-2008, I pushed up 69 tables after a couple days testing and experimentation.
(Got SSMS to spit out a query of table creation dates just now)
On the Access end, I had pushed up to a test db earlier, linked to that dead data and sorted out the grief with bit fields, True = -1 logic, timestamps and dbSeeChanges, so the frontend was mostly ready to go.

The first two days in production were still pretty snaky.
The users were downstairs and I was upstairs.
I did half the distance to the summit of Everest in those two days, because you can't catch everything in testing.
Never looked back.

OK.  Its become obvious that this is not going to be the quick fix that I was hoping it was going to be.  Since I only have part of two days to work on it, I think I am going to have to table this for a while.  In a few months, we have another project with this database, where we have to merge in another process with it (there are claims that are run through another database before they are manually imported to this one).  I think I should be able to roll these changes discussed here in with that update.

But I truly appreciate all the help provided here.  I learned a lot, and will be referencing this thread when I am making those updates.  I will try to spread out the awarding of points to all those who stuck with me through it all.

Thanks again!

When doing data design, the first phase should always be logical modeling only.  During this logical phase, it's 100% irrelevant whether ultimately Access or MySQL or whatever will be used to store the data.  The key thing is to model the data itself correctly, without regard to physical db restrictions/limitations/etc.  This applies even more to any application considerations -- there must be NONE of those during the logical design process.  The app is 1000% irrelevant.  Dbs and apps undergo wholesale changes over time, but the data lives on.

I know.  I always do that when I create my own databases.
Unfortunately, we sometimes inherit stuff that's a mess, at least from a design standpoint.

I understand.  But, whenever possible, you must ignore what is current and focus on what is right.  Just because you did it wrong last week and last year doesn't mean you should keep doing it wrong this month.

But, whenever possible, you must ignore what is current and focus on what is right.

Are you saying that I should ignore my other projects and focus on this?  I am not sure where you work, but if I worked under this credo, I would be out of a job pretty darn fast!

In a theoretical world of unlimited resources, we would all love to do that, and do it right away.  In a world of limited resources (time, money, etc), in doesn't always work that way.  Priorities come into play.  If I don't complete a high priority project because I instead spent time trying to clean-up a database, that in other's minds, was working just fine, that doesn't bode too well for me.

It is all about setting priorities.  This project is still on the list, but just has moved down the list a bit.  That happens when you have projects that your CFO and parent company are hot after!

@ScottPletcher
I don't disagree with that in theory.
In practice, the logical phase usually presents some options about how to model the data.
The options pursued inevitably impact the final result.
Take for instance hyperlinks.
You can create a normalized data structure that calls for a hyperlink to be stored for each record.
You then have choices.
Store it as hyperlink field in Access? -- except that rules out any other backend.
Many-to-many relationships are another.
You can model those as a junction table.  Every RDBMS now deals with those
Or you can model those as a series of one-to-many relationships.
Both are correct and workable.
But sooner or later, you are going to deliver a working product that will need to be maintained and probably extended.
And maybe not by you.
One hopes not to be cursed by one's successor.

There are choices -- and all of them correct -- to be made.
One makes them with the best foresight and most information you can muster.
And that invariably involves the totality of the environment.

Third normal form.
Everybody has to go there all the time.
Those who don't should be shot.
But it still can leave options.

Are you saying that I should ignore my other projects and focus on this?
No.  But there has been some strong disagreement about what the final data structure should be.  We all feel you should get that right  :) We just think our individual way is the right one :)
You'll get it figured out!

Nick67

Upsizing used a wizard in Access and creates an ADP (now deprecated)

Only if you chose that option.  It also just transferred the table schema and data to SQL Server.

However, when I try to change the table properties so that the ID field is now the Primary Key field instead of the Department field, Microsoft SQL Server Management Studio is giving me the following warning:

With small exceptions (this could be one) I use autonumber fields as the primary key.  But, what you are running into is the problem that occurs when you go from an existing schema, with data and RI and try to change the primary key.  The change is much bigger than that if the table is related to other tables as this one is.  You will also need to convert all the FK relationships to use the new autonumber.
ALWAYS make a backup before embarking on any schema change.
1. Delete any relationships currently defined that refer to the Department name.  But make a note of them since you will need to put them all back when you are done.
2. Add the new autonumber.  Do not name it "ID".   Give it a proper name such as DeptID.
3. Change the PK to be DeptID.
4. Add a unique index on the old Department name.  This is necessary to enforce the business rule that the DepartmentName must be unique.
5. For each table that had a relationship with tblDept, add a new long integer column named DeptID.
6. For each table that had a relationship with tblDept, create an update query that joins the old department name fields on the tables and updates the DeptID with the value from tblDept.
7. Once you are sure that ll the DeptIDs are populated correctly, delete the old FK columns that contain the department name.
8. Final step is to add back the RI.

As you can tell, I've done this before.  But there are tables where I will simply leave or define from scratch a simple alpha code.  State table is one of them.  Joins are irrelevant since the state table would always be loaded into memory as part of any execution plan.  Doing this eliminates one join that would always have to be made.  It's not like states change their names with any frequency.  I'm sure that in the history of the US, we can probably count the changes on one hand.