firstly dbforums is an excellent forum, I have had a brief look through some of the reply's to some members posts and most of the answers to the questions posted are excellent. I hope someone here can help me with what I am trying to create; a Project time recording WebApp, I am using Php for my front end and MySql for the back end. Below is my design so far, if someone could please take a look over and let me know if I am going in the right direction (if not point out why) or any advice, I would greatly appreciated it.
Two departments, Two managers, 12 employees.
One manager (mgr) is assigned one department (dept), one department is assigned one manager.*
One manager manages many employees but many employees can only have one manager.*
Many employees can have many projects.*
Projects can have many employees.*
Many employees can have many timesheets.
Timesheets can have many employees.
Timesheets can have many Projects and Projects can have many Timesheets. There are several past and will be several future projects.
Projects that will have a reference number no longer than four numbers.* Timesheet will be needed to record time spent on a project and who spent it.* EU Directive requirement will be incorporated into the system in accordance with the 2003 EU directive entitled to a break.
Basic characteristics of entities: Manager has one name, department can be either as or 3d. Employee has only one employee id number, timesheet can record several times but has only one Prj no, and Timesheet type can describe what type of timesheet it is.
I have listed the entities important to my database, these entities are generic, fundamental and unitary.
Scope of the database schema
recording/calculating timesheet infromation.
from the elements I created a matrix; matrix.png, below link
from the matrix I created the relationships Relations
A Department Manages an Employee
An Employee Works For a Department
An Employee Records on a Timesheet
An Employee Works On a Project
An Employee Has a Type
An Employee Has a Catogory
An Employee Takes a Hoiliday
An Employee Takes a Sickday
An Employee Is Assigned a Lock
An Employee Posted a Message
A Timesheet Records Employee
A Timesheet Records Project
A Project Is Assigned to Department
A Project Is Assigned to Employee
An Employee Type
An Employee Catogory
A Holiday Record Employee
A Sickday Record Employee
A Lock Is Assigned to Employee
A Message Posted to Employee
from the relationships I created a basic ERD: timesheeterd.png, below link
If anyone has any queries, please feel free to ask.
Programs I am using Notepad++, OpenOffice.org, XAMMP, eclipse IDE and unfortunately I have to use Visio to create my erd, so far I have not found a suitable open source alternative.
thanks for yr comment and yes, well spotted I should have mentioned in my first post; I created the first erd with only the most important elements. I will add the rest of the elements in the next diagram.
What I am concerned with is the relationship with the element/entity timesheet what type of relationship should it have with the other entities.
Employee 1-N (one to many) timesheet.
Department 1-N (one to many) project
Project M:N (many-many) timesheet.
This basic design is the user logins in and clicks checkin button and then works on a project, when finished working on project user logins back in selects project worked on from a list then clicks clock out and viola these times are stored in a database.
I am new to designing/building databases and thought planning was a key element to designing a well structured database, so if any anomalies arise they can be dealt with at this phase. I come from a 3d design background and this is how I have always worked requirements, plan, design and build.
below is a link to a more comprehensive erd, with all the elements, however i found in visio when I tried to create certain relationships it did not work the way in which I wanted, I hope this makes sense.
Looking for a little guidance on this erd, please.
here are the relationships;
A Department Manages Employees - one to many
Employees Work For a Department - many to one
An employee manages a Department - one to many
a department has one employee as a manager - one to many
Employees Record on Timesheets - many to many
Employees Work on Projects - many to many
An Employee Has Types - one to many
An Employee Has Categories - one to many
An Employee Takes Holidays - one to many
An Employee Takes Sickdays - one to many
An Employee Assigned Locks - one to many
Employees are Posted Messages - many to many
A Timesheet Records Employee - one to many
A Timesheet Records Project - one to many
Projects are Assigned to Departments - many to one
A Project Is Assigned to Employee - one to many
A Holiday Records a Employee - one to many
A Sickday Records a Employee - one to many
A Lock Assigned to a Employee - one to many
Messages are Posted to Employees - many to many
Whether you use Chen's or crow's foot conventions depends on your assignment or preference. What's more important is that you understand the meaning of the elements, and work carefully to compare them with your requirements.
In your crow's foot diagram, you show two relationships between timesheet and project. This is contradictory, a crow's foot relationship is already bidirectional. It seems this is due to confusion over the many-to-many relationship. Many-to-many relationships are usually implemented by reifying the relationship as an entity and then creating one-to-many relationships to it. See my example below.
Still in your crow's foot diagram, you have some problems with primary keys and foreign keys. All your foreign keys are indicated as primary keys, most shouldn't be.
Your Chen diagram is in better shape, probably because leaving out the attributes allowed you to focus on the relationships between the entities. Still, there are some issues - for example, you relationships between employees and departments don't match your written description (which also needs attention - you mention the management relationship 3 times, and work relationship only once).
First tidy up your written description, then correct your Chen diagram and break up the many-to-many relationships. Next, convert that into a crow's foot diagram, and make tables for rectangles as well as diamonds, except for one-to-one relationships, and decide carefully where to assign the attributes. Reifying relationships isn't a general rule, but in this case it may help you to normalize your tables.
firstly thanks for looking over my design and the excellent feedback.
I have made some changes with yr feedback in mind.
I editted the erd matrix, the chen erd and the crows feet erd, after I posted my problem I decided to use workbench I found this program better overall than Visio.
I see from yr diagram in the last post you have zero or more relationships and assositive entities. I hope I have elimated the need for these in my updated design, however I am wondering are these design features necessary in all design?
Two departments, Two managers, 12 employees.
One manager (mgr) is assigned one department (dept), one department is assigned one manager.
An employee has many Projects.
An employee can have many timesheets.
A Timesheet can have many Projects.
ERD matrix see below
From the matrix I created the relationships;
A Department is assigned an Employee. 1:N
A Department is run by a Manager. 1:1
A Department is assigned a Project. 1:N
An Employee works for a Department. 1:N
An Employee is a Manager. 1:1
An Employee records a Project. 1:N
An Employee has a Type. 1:1
An Employee has a Catogory. 1:1
An Employee has a Hoiliday. 1:N
An Employee has a Sickday. 1:N
An Employee is assigned a Lock. 1:N
An Employee is posted a Message. 1:N
A Manager runs a Department. 1:1
A Timesheet records Employee. 1:N
A Timesheet records Project. 1:N
A Project is assigned a Department. 1:N
A Type has an Employee. 1:1
A Catogory has an Employee. 1:1
A Holiday records an Employee. 1:N
A Sickday records an Employee. 1:N
A Lock is assigned an Employee. 1:N
A Message is posted to an Employee. 1:N
Basic Db design.
Employee logins in and selects checkin button and begin work on a project which assigned by the department manager (by message, verbally, by email) giving the manager and employee move flexiblitiy to work on any project at any time. When finished working on project user logins back in selects project worked on from a list. The list is created form selecting either the last project an employlee has worked on or the list is select from the projects the deparment for the employee to choose employee clicks clock out and voila these detalis and times are stored in a database Timesheet table.
from this I created the roughERD3 and then the ERDwb see below.