Database Design - Entities in Access (1 Viewer)

[mosh]

Hello All,

I have created the ERD for my project, here is the scenario:

Employee is allocated to one manager whilst a manager can manage many employees.
An employee can work just on one type of shift whilst a shift can hold many employees
An employee can book many holidays whilst a holiday can contain more than one employee (6)
Employee can work on many systems whilst a system can be worked on my many employees (the systems will be present in the employee table)
Employee can have a certain amount of holidays allocated; a profile of holiday can be allocated to one employee, while an employee can have one profile of holiday allocation.
An employee can be off from work more than once in any given day (employee can have holiday in the first half of the day and then be sick for the rest of the day (pm).

Here is a pic of the ERD:



Can you see any relational problems?

Also when I create a holiday for an employee, how does the holiday booking table update itself with the records?

Thanks
________
Web Shows

 

[aziz rasul]

Seems OK. Can't see anything obviously wrong.

 

[Mike Smart]

Hello All,

I have created the ERD for my project, here is the scenario:

Employee is allocated to one manager whilst a manager can manage many employees.
An employee can work just on one type of shift whilst a shift can hold many employees
An employee can book many holidays whilst a holiday can contain more than one employee (6)
Employee can work on many systems whilst a system can be worked on my many employees (the systems will be present in the employee table)
Employee can have a certain amount of holidays allocated; a profile of holiday can be allocated to one employee, while an employee can have one profile of holiday allocation.
An employee can be off from work more than once in any given day (employee can have holiday in the first half of the day and then be sick for the rest of the day (pm).

Here is a pic of the ERD:



Can you see any relational problems?

Also when I create a holiday for an employee, how does the holiday booking table update itself with the records?

Thanks

Hi Mosh

Great post. Very good idea to post your schema here too, if you get the schema dead right the reports/forms etc take care of themselves!

Your schema looks good. Here's my first thoughts:-

Employee/Manager realtionship, perfect
Employee/Shift relationship perfect too
Employee/Holiday relationship may have a problem.

First of all, Employee/Holiday isn't a many-to-many unless you intend to create things like bank holidays in this table and (potentially) allocate them to many employees. I'll assume that this is the case for the discussion (ie it is a many-to-many).

You appear to have a foreign key (payroll_number) in Holiday. Why is it there? It doesn't seem to be needed since you already know the payroll_number from the hol_id.

Allocated_holidays should't be there. Whether the holiday is allocated is an attribute of the relationship between the holiday and the employee (ie it should be a field in the holiday_booking table as Allocated (Yes/No).

You also don't need a leave table at all (in fact your reports will be quirky if you left it in). Instead think of all down-time as being generic. The Holiday table (you could call it leave instead) can deal with both sick absence and holiday absence and think how much easier the reports will be!

> Also when I create a holiday for an employee, how does the holiday booking table update itself with the records?

It doesn't. This table records the attributes of the relationship itself (between an employee and a holiday).

EG I create a record in holday for St Patricks day as we close that day. This requires an entry in holday_booking for all employees.

I create a record in holiday for fred smith who is on holiday from 1st-15th may. I create the record in Holiday, I create a record in Holiday Booking to associate it with Fred, it has status pending. When approved the status is flipped to approved (status being an attribute of holiday_booking).

Once you have the schema straight you can develop a nice form with holiday_booking as a sub-form allowing all holiday allocation to be done from one place.

Another thing you might consider. Check out my design guidelines (published free on my site). If you re-write the schema with regard to these rules I know that you'll find that it serves you a lot better.

Good luck and let me know if any of this isn't clear.



You create a holiday

 

[Dennisk]

couple of points
Primary keys should be the first column. so the Holiday_Booking Table should be Hol_ID fist then the foreign key PayrollNumber.

Personally I would remove all lookup tables from your relationship diagram.
If cascading deletes are set for the relationship between the shift table and the payroll table and a shift pattern is deleted then all the Payroll records will be deleted.

Finally I developed a many to many table a couple of years ago (I did remove it eventually) and I seem to remember using an SQL statement to update the table although I could be wrong. If I can find that old version over the weekend I will let you know.

 

[mosh]

thanks for posting, some good help here.

Here is the updated schema and also the updates:



Employee ? payroll_number, manager_id, allocation_id, shift_type, forename, surname, department, fte, beacon, C1, CGABS, TGB, SDESK, Credit

Manager ? manager_id, forename, surname, start_date, level, department

Shift ? shift_type, shift_duration, hours, lunch

Holidays Allocated ? Payroll_number, allocated, hours_left (derived attribute)

holiday_booking - hol_id, payroll_number

Holiday ? hol_id, payroll_number, leave_type, date_booked, part_of_day, initial_request_date, pre-booked, hours_booked, total_absent (derived attribute)


I have removed the "Leave" table and inserted the field "leave_type" in table "holiday".

As for the allocated_holiday table, I thought I could insert the days_allocated field into the employee table, since an employee is allocated a certain amount of days holiday in a year, this sound feasible?

I am slightly concerned about the primary key field type for hol_id.

In the holiday_booking table it is set as autonumber, but in the holiday table itself it is also set as autonumber,is this correct? or should the hol_id in holiday_booking should be "number" and not auto-number?

I need some help on entity relationship casecade update / delete.

Thanks for your help.
________
Cannabis Seeds

 

[Mike Smart]

Hi Mosh

I see you've re-designed! Did you read the design guidelines on my site? There's still some basics that could be improved.

> I have removed the "Leave" table and inserted the field "leave_type" in table "holiday".

Perfect

> As for the allocated_holiday table, I thought I could insert the days_allocated field into the employee table, since an employee is allocated a certain amount of days holiday in a year, this sound feasible?

Days allocated is definitely an attribute of an Employee so that's correct.

> I am slightly concerned about the primary key field type for hol_id.

In the holiday_booking table it is set as autonumber, but in the holiday table itself it is also set as autonumber,is this correct? or should the hol_id in holiday_booking should be "number" and not auto-number?

It should be number. Hol_id is a foreign key in the holiday_booking table.

> I need some help on entity relationship casecade update / delete.
You can forget about cascade update if you follow my guidelines. The feature is there simply to support meaningful primary keys.

Cascade delete means that when you delete a manager you'd delete all of the employee records associated with the manager. In your schema you might want to delete all employee holidays when you delete an employee but, in real-world systems, you'd probably want to keep the employee record for audit purposes and simply flag the employee as inactive (via a field in the employee table) rather than delete the record.

Let's have a more in-depth peek at your new schema:

Employee-Manager is fine
allocated holidays shouldn't be there
shift is fine
holiday_booking may not be needed. Depends on whether you need to associate the same holiday with many employees or not. Do you?

IOW is it :-

One Employee may have many holidays but a holiday is only ever attached to one employee

OR

One Employee may have many holidays and one holiday may be associated with many Employees.

Look forward to your next draft (and do read my design guidelines)!

Have fun

 

[mosh]

hi,

Thanks for the reply mark.

An employee can take many holidays, but a holiday can contain many employees (6 employees are allowed annual leave on any given day).

Is there another solution to my ERD for this?

thanks.
________
The View Condo

 

[Mike Smart]

hi,

Thanks for the reply mark.

An employee can take many holidays, but a holiday can contain many employees (6 employees are allowed annual leave on any given day).

Is there another solution to my ERD for this?

thanks.

Mike... not Mark!

That's not what I meant. Do you want to create a record called (for example) "Independence Day" and link it to several employees or will every record be linked to one, and only one, employee. In this case you can still have six records, each linked to different employees, each for different, or the same, date ranges that may overlap.

 

[mosh]

Mike... not Mark!

That's not what I meant. Do you want to create a record called (for example) "Independence Day" and link it to several employees or will every record be linked to one, and only one, employee. In this case you can still have six records, each linked to different employees, each for different, or the same, date ranges that may overlap.

Apologies Mike,

Thats right, every record will be linked to one and onle employee, so one employee will book the day off, and then another booking it off on the same day.
________
WETDANNA

 

[Mike Smart]

Mosh

In that case Payroll_number becomes a foreign ksy in the Holiday table and Holiday_booking goes in the dustbin.

 

[mosh]

so mike, even if one day can have many bookings from different employees, I can still get rid of holiday_booking?

Thanks
________
VAPORIZER-INFO

 

[Mike Smart]

so mike, even if one day can have many bookings from different employees, I can still get rid of holiday_booking?

Thanks

Yes. Payroll_number is a foreign key in the Holiday table so you can have lots of different records in the holiday table, even if they are all on the same day, linked to as many employees as you want.

I won't consider business rules yet but you'd probably want to do some validation as holidays are assigned to employees... but get the schema right first!

Post up your schema again when you have it finalised.

 

[mosh]

Hi Mike,

here is the finalised schema;

Employee ? payroll_number, manager_id, shift_type, days_allocated,
forename, surname, department, fte, beacon, C1, CGABS, TGB, SDESK, Credit, Status

Manager ? manager_id, forename, surname, start_date, level, department

Shift ? shift_type, shift_duration, hours, lunch

Holiday ? hol_id, payroll_number, leave_type, date_booked_Frm, date_booked_to, part_of_day, initial_request_date, declined, hours_booked, notes, total_absent (derived attribute)

If an employee has left the business, records will still need to be kept. a "Status" field has been created in employee table if an agent leaves or goes on a sabbatical etc..

Here is the final ERD.


I want to create some derived attributes but finding it difficult:

Total number of hours used ([days_allocated]*24- SUM of total hours used (hours_booked) by one employee).

Same calculation but for hours remaining.

Thanks.
________
Sexy_Goddess4Uxx

 

[Mike Smart]

Hi Mike,

here is the finalised schema;

I want to create some derived attributes but finding it difficult:

Total number of hours used ([days_allocated]*24- SUM of total hours used (hours_booked) by one employee).

Same calculation but for hours remaining.

Thanks.

Dear Mosh

Your schema is a lot better now. I'd still like to see you do something about your field and table naming but you shouldn't have any problems reporting anything you want from your present schema.

By "derived" attributes I'm assuming that you mean calculated fields in your queries. That is a topic for the Queries forum and I'm sure you will get plenty of help there if you post your schema along with the query results you need.

Glad this helped you

 

[mosh]

Dear Mosh

Your schema is a lot better now. I'd still like to see you do something about your field and table naming but you shouldn't have any problems reporting anything you want from your present schema.

By "derived" attributes I'm assuming that you mean calculated fields in your queries. That is a topic for the Queries forum and I'm sure you will get plenty of help there if you post your schema along with the query results you need.

Glad this helped you

Thanks Mike. What kind of field / name changes would you do?

Regards
________
Glass pipe pictures

 

[Mike Smart]

Thanks Mike. What kind of field / name changes would you do?

Regards

Download the quality standard from my site, read it carefully and then re-name your fields/tables as recommended. If you do that and then post back your revised schema I'd be happy to give you more input.

 

[mosh]

I have just researched more into the employee / shift relationship.

An employee can work many shifts in a week (for example on monday 8am-4pm, Tuesday 10-6pm)

This means in the current schema I cannot do this.

A solution I thought of was to have Monday - Sunday as fields in the employee table and then have a dropdown of the shifts as values?

Will this work?

Thanks for your help.

Mos
________
Live Sex Webshows

 

[Mike Smart]

I have just researched more into the employee / shift relationship.

An employee can work many shifts in a week (for example on monday 8am-4pm, Tuesday 10-6pm)

This means in the current schema I cannot do this.

A solution I thought of was to have Monday - Sunday as fields in the employee table and then have a dropdown of the shifts as values?

Will this work?

Thanks for your help.

Mos

Your present schema allows any number of shifts to be attached to an employee but I think you now want to model the idea of the same shift being attached to many employees. For example: Joe works 9-5 on monday and 12-5 on Tuesday. The same 9-5 shift is also worked by fred and bill on Monday and the 12-5 Tuesday shift is also worked by Andy and Jeff.

If this is the case you have a many-to-many relationship and need to add a link table called EmployeeShift with a primary key made up of EmployeeID and ShiftID. You can then add attributes to this table better describing the relationship between the Employee and Shift. For Example, the EmployeeShiftDay could indicate which day of the week the shift was worked. In this scenario the 9-5 entry in the Shift table would have three records in EmployeeShift with foreign keys for Fred and Bill and the EmployeeShiftDay field set to Monday. There would be another 12-5 record in the Shift table and three more records in EmployeeShift for Joe, Andy and Jeff with the EmployeeShiftDay set to Tuesday.

Set it up and it should be clear whether this is what you are trying to do, if not, explain the problem more precisely.

 

[mosh]

Hi,

Thanks for the reply Mike, It is a m-m relationship, but I have just created a table EmployeeShift (consisting of payroll_number / shift_type as primary keys), and employeeshiftday as the extra attribute.

It seems as if the relationships aren't working as 1-M, here is a pic of what I have done.



Why is it not 1-m m-1? Is it because there is data in the shift / employee table?

thanks
________
Kartess

 

[Mike Smart]

Hi,

Thanks for the reply Mike, It is a m-m relationship, but I have just created a table EmployeeShift (consisting of payroll_number / shift_type as primary keys), and employeeshiftday as the extra attribute.

It seems as if the relationships aren't working as 1-M, here is a pic of what I have done.



Why is it not 1-m m-1? Is it because there is data in the shift / employee table?

thanks

Constrain the shift/EmployeeShift relationship and the error should be apparent.