Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to learn entity relationship diagram, following question is the objective.

Information consulting firm has number of offices with employees. The employees can move offices depend on the project, but location is always recorded. This consulting firm manage number of projects and have vast base of clients. The company's success is depend on assigning relative employee with relative skill and location. The time an employee spends on project is recorded each day. This include time for travel and break time etc. To reduce time for travel they try to allocate employees from the nearest office possible. Draw Entity Relationship diagram and entity listing with attributes.

I created entity relationship diagram by using Visio. Please comment on my attempt.

Office

Address
telephone
office manager

Employees 

name
address
job
hourly rate
start_date
location etc

Projects

Name
Manager
Start Date
End Date
Estimated Cost
Quote
Actual Cost

Client
Name
address
telephone
e-mail
contact

Assignment

name
Employee name
Date Assigned
Hours spent

Here is my attempt in this image:

enter image description here

share|improve this question

1 Answer 1

Good. The essentials are there. A first rough list of entities is to take the nouns of the story - office, employee, project, clients, assignment, skill. Each table has a unique, sequential, meaningless key. Columns are given descriptive names. Here are the suggestions by entity.

office> The manager is an employee and should have a separate foreign key relationship with manager_employee_id. There is a many-to-many with employee. Avoid M-to-M relationships. In this case, an office has many employees. But an employee has one home office. I suggest naming fields uniquely with a common suffix describing the domain, office_address, instead of address.

employee> The job_id should be a foreign key to job instead of title. The office_id should be a foreign key to office instead of location. The column names should be hire_date instead of the start_date to avoid confusion with the project start_date. Use employee_address instead of address based on the domain suffix rule.

project> The project manager is a separate foreign key to employee pm_employee_id. What is the business difference between estimated_cost and quoted_cost? Make sure there is a foreign key field to match every relationship line. Client_id should be the foreign key. Office_id should be a foreign key.

assignment> Good, this is an intersecting table between project and employee. I suggest renaming the date to assigned_date to keep the suffix consistent. Notice that the hours_worked are in this table with the assigned_date. The assigned_date happens once. But the hours worked happen daily.

daily_assignment> Maybe there should be a separate child table called daily_assignment with its own primary key, a foreign key to assignment, and the worked_date and hours_worked as fields. This tracks each item of data uniquely at its own level.

client> I suggest renaming the address to client_address.

There are ways to make it more complicated. But I think this model works well. It should give you flexibility to make business changes, yet still be simple enough to code.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.