month
working_days per month (Example: 23)
target_hours per day (Example: 8.5)
gross_hours per month (calculation: working_days*target_hours)
holiday_description (there could be more than one in a month)
payed_hours (Example 8.00 for one holiday)
target_working_hours (calculation: gross_hours - payed_hours)
I have some questions:
Should I create a table for every year like
table_year
yea_id
yea_number (integer)
and a month table like
table_months
mth_id
mth_description (varchar)
and probably even a table_holidays because I could have 2 or 3 holidays in a month?
I there a way to calculate the working_days of a month? All days of a month minus the weekends?
As the name and the founder intended, Entity-Relationship-Modelling is a modelling technique where you try to model the part of the real world you would like to be able to handle in your software application. The method gives you the tools to design entities and how they are related (in your context) to each other.
I am saying because I don’t think it’s possible to give a sesible answer to your request. You should describe much more of what your software is going to do. This usually can’t be done by defining some fields. Fields are coming into play if you think about entities and how they should generically described, done by all the mandatory attributes associated with this entity. This is quite a time consuming process, as one has to think a lot about the planned application and it’s stored data, and, as a consequence what functionality you can apply to this data.
One thing I can say that it may not be too easy to find a good data model with the temporal (date/timing) information you would like to store. The reason is you get many (so called) boundary clashes, as one month has NOT exactly four weeks, and a varying number of days and even a year has not a constand number of days.
So I think it’s quite important of what exactly is how important in your application.
You could model for example a year built up of n months (12) and each month is again built up by n days (variable). A day has some mandatory working hours (and may be non working hours). But this may only be true if it’s not a saturday or sunday. The question remains, what can be modelled should be, and what can not be modelled must be done by funcionality (less elegant and more expensively, in terms of time/money)
Sorry of not being able to give you a more precise answer.
Best regards, Robert
cookie:
Hi
For my basic concept I have following fields:
month
working_days per month (Example: 23)
target_hours per day (Example: 8.5)
gross_hours per month (calculation: working_days*target_hours)
holiday_description (there could be more than one in a month)
payed_hours (Example 8.00 for one holiday)
target_working_hours (calculation: gross_hours - payed_hours)
I have some questions:
Should I create a table for every year like
table_year
yea_id
yea_number (integer)
and a month table like
table_months
mth_id
mth_description (varchar)
and probably even a table_holidays because I could have 2 or 3 holidays in a month?
I there a way to calculate the working_days of a month? All days of a month minus the weekends?
I’m attaching a file (german) to give some more insight about what i’m intending to store into my database. The data is needed afterwards to calculate business statistics and salaries.
I looked at your Soll-Arbeitszeit sheet. I think, if you really only want to get this sheet without furter enhancements, it may actually not be worth to do more than a table containing all the attributes you proposed (inlc. a year attribute) and use values lists for attributes like month or working_days per month. As you have only sums of columns, you can do it as report or on a form.
Hope this helps, Robert
cookie:
Hello Robert
I’m attaching a file (german) to give some more insight about what i’m intending to store into my database. The data is needed afterwards to calculate business statistics and salaries.