Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Help with Project Time Recording Database

    Hi all,

    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.

    Business Rules
    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.

    Database Elements
    Department
    Employee
    Timesheet
    Project
    Employee Type
    Employee Catogory
    Holidays
    Sickdays
    Locks
    Messages

    from the elements I created a matrix; matrix.png, below link

    http://strayjoconnor.files.wordpress.../03/matrix.png

    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

    http://strayjoconnor.files.wordpress...mesheeterd.png

    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.

    Regards,
    Steo.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you're just building an app, you don't really need an ERD

    however, if you really need an ERD then it's gotta be a homework assignment for school

    your ERD appears to be missing at least half of your database elements --

    Department
    Employee
    Timesheet
    Project
    Employee Type
    Employee Catogory
    Holidays
    Sickdays
    Locks
    Messages
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2012
    Posts
    4

    employee, project, timesheet relationship?

    Hi r937,

    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.

    http://strayjoconnor.files.wordpress...012/03/erd.jpg

  4. #4
    Join Date
    Mar 2012
    Posts
    4

    help with database

    Hi all,

    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

    this is a link to the erd http://strayjoconnor.files.wordpress...3/rougherd.jpg

    any help would be greatly appreciated.

  5. #5
    Join Date
    Feb 2012
    Posts
    76
    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.
    Attached Thumbnails Attached Thumbnails projecttime.jpg  
    Last edited by reaanb; 03-28-12 at 17:13.

  6. #6
    Join Date
    Mar 2012
    Posts
    4

    Database design changes

    Hi reaanb,

    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?

    Business Rules
    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.
    Attached Thumbnails Attached Thumbnails ERDmatrix.jpg   roughERD3.jpg   ERDwb.png  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •