Who knows about Databases - ERD

[Chadsxe]

Hi,

Let me start by saying I am new to database design. I have been programming for 5 years now but recently felt like it would be of worth to learn about databases and database design. I picked up a few books and am now working my way through them. So without further ado, hear is my first question.

I am currently trying to understand and learn how to use the Entity Relationship Model. One of the books I am reading asks me to conceptualize a basic ERD showing the relationships between a Department, Employee, and Manager. The book offered me up these business rules?.

Each department has many employees, but each employee belongs to one department.

Each department is managed by one employee, and each of those managers can manage only one department.

This is my first attempt at it

So what I am confused about is if a manager is an employee how do I represent that relatiomship.

Regards

Chad

[Aergan]

3 entities: Department, Role & Employee

Role has Unique Identifier, Data

Department has Unique Identifer, Data

Employee has Unique Identifier, Data, Role Identifier (this is the relationship to the Role entity), Department Identifier (this is the relationship to the Department entity)

Employee is at the top, the other entities make up what makes the employee in the organisation.

[AJR1]

I have to be honest I am struggling a little with the concept proposed however my solution would be the following, I have ignored one part of your concept as it is fundamentally flawed in my opinion.

Each department has many employees, but each employee belongs to one department. Each department is managed by one employee, and each of those managers can manage only one department.

Department and Manager have a one to one relationship, Manager and Employee should have a one to many relationship and Department and Manager should have a One to One relationship if I logically subtract the requirement for an employee to belong to only one department, you wouldn't implicitly define this within an entity relationship diagram, it would be assumed based on the one to many that currently exists between Department and Employee, therefore my proposal is the following.

You'll notice this is actually a UML class diagram but it is just to illustrate the point about the way the relationships are setup.

[AJR1]

Correction* Department and Manager have a one to one relationship, Manager and Employee should have a one to many relationship and Department and Employee should have a One to Many relationship.

[GPF]

You can show the relationship of employee to manager with one table by using a self-join. In other words, a relationship is made back to the same table.

There would be a field that held an ID to the employee's manager, and that self-join would point back to pull the manager's information. Eventually, though, you'd end up with at least one record with no manager (owner, or top boss), so your code would have to take that into account.

http://en.wikipedia.org/wiki/Join_(SQL)#Self-join

[basques]

Employee Table

[Columns]

EmployeeId : Unique primary Key

DepartmentId : This is the foreign key to the Department Table

Other data...

Department Table

[Columns]

DepartmentId: Unique primary Key

Other data...

This one is the relation table keeping track of one to one relation between a department and an employee assigned as a manager

Department Managers

[Columns]

DepartmentId

EmployeeId

[Chadsxe]

Manager and Employee should have a one to many relationship

By saying this then you would have to assume that every Employee has to be a Manager. I don't know you would rectify this with UML, but in Crows Foot notation you would do soemthing like.

Manager -|-0--------------< Employee

Which basically says an employee does not have to be a Manager, but has the option to be exactlly 1 Manager.

You can show the relationship of employee to manager with one table by using a self-join. In other words, a relationship is made back to the same table.

There would be a field that held an ID to the employee's manager, and that self-join would point back to pull the manager's information. Eventually, though, you'd end up with at least one record with no manager (owner, or top boss), so your code would have to take that into account.

http://en.wikipedia.org/wiki/Join_(SQL)#Self-join

I am just now learning how to represente this with crow's foot. The only problem I see is that it does not take into any account that a Manager might be it's own entity. If you self-join an employee and a department to represent this, then the idea a Manager might have it's own special attributes in addition to those of Employee gets thrown out the window.

Thanks

Chad

[Mattytommo]

There seems to be a redundancy in that design (although pretty much everybody in databases differs in opinion).

You only need two tables,

Managers ARE Employees, therefore it would make more logical sense that they both were in the same table and you used an attribute like Employer Level to determine e.g. Standard, Manager etc. This gives you more future scalability too incase you wanted to add like a Supervisor.

Employee & Department.

If however the spec said that Managers have different details to Employees all together, then as part of the first form of normalisation you would remove the repeating group (but don't worry about that yet, your head may explode).

[Aergan]

There seems to be a redundancy in that design (although pretty much everybody in databases differs in opinion).

You only need two tables,

Managers ARE Employees, therefore it would make more logical sense that they both were in the same table and you used an attribute like Employer Level to determine e.g. Standard, Manager etc. This gives you more future scalability too incase you wanted to add like a Supervisor.

Employee & Department.

If however the spec said that Managers have different details to Employees all together, then as part of the first form of normalisation you would remove the repeating group (but don't worry about that yet, your head may explode).

Difference between a manager and a normal employee is their role :)

[AJR1]

By saying this then you would have to assume that every Employee has to be a Manager. I don't know you would rectify this with UML, but in Crows Foot notation you would do soemthing like.

Manager -|-0--------------< Employee

Which basically says an employee does not have to be a Manager, but has the option to be exactlly 1 Manager.

I am just now learning how to represente this with crow's foot. The only problem I see is that it does not take into any account that a Manager might be it's own entity. If you self-join an employee and a department to represent this, then the idea a Manager might have it's own special attributes in addition to those of Employee gets thrown out the window.

Thanks

Chad

"Crows Feet" Notation came about to simplify the representation of relationships within ERD's it is kind of a standard I guess. I wanted to elaborate on my point a little here, the fundamentals of database design is that through the correct use of database normalisation (if your not familiar with that term you very soon will be) the data contained within the database should have no redundancy by design. In simple terms, no data within the database should be duplicated elsewhere or as I prefer to look at it, over engineered, its just as bad as data redundancy.

What you have been asked to conceptualize in my opinion contains redundancy. Back to the point regarding employees and managers, I was always taught throughout my computer science degree to look at everything in OO (Object Orientation) so if you consider that an employee can be a manager according to the rules of normalisation you shouldn't therefore need a manager table, as guess what, this creates redundancy, your duplicating the data twice.

If an employee can manage a department, and only one employee can manage a department then this should be a one-to-one relationship, but as a department can also contain many employee's this is where it gets a little bit tricky and I think you can make a big mistake right here unless you think of it like this.

The purpose of ERD's is to visualise the relationship between classes or tables if your non object orientated. It was developed so that at a glance you can see a department can have many employee's therefore when constructing the tables within the database you would know what kind of relationship to define and as such whether or not to invoke a foreign (sometimes known as a concrete or compound) key, which is a second unique identifier within a table.

An ERD is not designed to show authorisation levels, and if you think "Department" in an object orientated way it would have attributes and operations. Attributes would be aspects that apply to only a Department so things like stock, rooms, chairs, tables, employee's I suppose, an operation are things the Department can do, so this would be order stock, move chairs, fire employees, move tables.

Therefore if an Employee can be a Manager this would be an attribute of the Employee class, and its relevant operation would be "Promote to Manager" "Demote to Employee", you wouldn't have a separate table to house this information as your duplicating the data.

Apologies for the long winded response, but I would be doing you a disservice if I didn't explain it fully.

You can define database structure using either crows feed in ERD's or you can try UML, if I was you just starting out now, I would go with UML its a standard and is far easier to represent than an ERD with crows feet it can get extremely confusing with some very complex database structures, imagine a hospital for example and trying to display that with crows feet!.

Look at my original post above, thats a very simple UML diagram using object orientated classes that contain attributes and operations.

There seems to be a redundancy in that design (although pretty much everybody in databases differs in opinion).

You only need two tables,

Managers ARE Employees, therefore it would make more logical sense that they both were in the same table and you used an attribute like Employer Level to determine e.g. Standard, Manager etc. This gives you more future scalability too incase you wanted to add like a Supervisor.

Employee & Department.

If however the spec said that Managers have different details to Employees all together, then as part of the first form of normalisation you would remove the repeating group (but don't worry about that yet, your head may explode).

+1 Agreed

Difference between a manager and a normal employee is their role :)

+1 Agreed - this is where the attributes and operations come in from UML / Class Diagrams.