Обсуждение: need help in designing a database.

Hi

I'm writing an application that calculates working hours for billing.
there are three levels of billing. one for regular hours, one for
evening/nights, and one for saturdays/holidays.

My first decision is wether I make these calculations in a database view
(If I understand correctly, it would be calculated only once - when
creating the entry or modify it - or am I wrong?) or just put the
regular working hours in the database, and calculate it in the application
(and this way it would be calculated every time I view the page)?

If I'm right and it's better to put it in the database, I really need help
designing the database... (of-course it's an open-source project).

my idea is to create one table that define default hours/days ranges for
the three levels of billing, one table to define client specific ranges, a
table with charging information (charge per hour for every level of
billing for every customer), tables that define custommers, and job
details and a final view that summerize everything.

my main problem is how do I define an hour range from friday 15:00 to
sunday 08:00, and how to create a function that compares the working hours
with this range?

thanx
--
Haim

Haim Ashkenazi wrote:
> Hi
>
> I'm writing an application that calculates working hours for billing.
> there are three levels of billing. one for regular hours, one for
> evening/nights, and one for saturdays/holidays.
>
> My first decision is wether I make these calculations in a database view
> (If I understand correctly, it would be calculated only once - when
> creating the entry or modify it - or am I wrong?)

Wrong. A view is just a way of transforming one query into another - it
re-runs the query every time.

 > or just put the
> regular working hours in the database, and calculate it in the application
> (and this way it would be calculated every time I view the page)?
>
> If I'm right and it's better to put it in the database, I really need help
> designing the database... (of-course it's an open-source project).
>
> my idea is to create one table that define default hours/days ranges for
> the three levels of billing, one table to define client specific ranges, a
> table with charging information (charge per hour for every level of
> billing for every customer), tables that define custommers, and job
> details and a final view that summerize everything.
>
> my main problem is how do I define an hour range from friday 15:00 to
> sunday 08:00, and how to create a function that compares the working hours
> with this range?

Hmm - sounds to me like you either:
1. Need to allow some time to go and learn the basics of relational
theory, and then some SQL and PostgreSQL admin.
2. Find another team-member for your project who has these skills.
3. Find another project that has solved the same problems. Join that
project or re-use elements (licence allowing).

Now, #3 seems to be the best approach to me. Any timesheet/billing
application will have to deal with the sort of problem you're
describing, and it makes sense to learn what approaches others have tried.

--
   Richard Huxton
   Archonet Ltd

On Mon, 28 Nov 2005 10:32:53 +0000, Richard Huxton wrote:

> Haim Ashkenazi wrote:
>> Hi
>>
>> I'm writing an application that calculates working hours for billing.
>> there are three levels of billing. one for regular hours, one for
>> evening/nights, and one for saturdays/holidays.
>>
>> My first decision is wether I make these calculations in a database view
>> (If I understand correctly, it would be calculated only once - when
>> creating the entry or modify it - or am I wrong?)
>
> Wrong. A view is just a way of transforming one query into another - it
> re-runs the query every time.
>
>  > or just put the
>> regular working hours in the database, and calculate it in the application
>> (and this way it would be calculated every time I view the page)?
>>
>> If I'm right and it's better to put it in the database, I really need help
>> designing the database... (of-course it's an open-source project).
>>
>> my idea is to create one table that define default hours/days ranges for
>> the three levels of billing, one table to define client specific ranges, a
>> table with charging information (charge per hour for every level of
>> billing for every customer), tables that define custommers, and job
>> details and a final view that summerize everything.
>>
>> my main problem is how do I define an hour range from friday 15:00 to
>> sunday 08:00, and how to create a function that compares the working hours
>> with this range?
>
> Hmm - sounds to me like you either:
> 1. Need to allow some time to go and learn the basics of relational
> theory, and then some SQL and PostgreSQL admin.
> 2. Find another team-member for your project who has these skills.
> 3. Find another project that has solved the same problems. Join that
> project or re-use elements (licence allowing).
>
> Now, #3 seems to be the best approach to me. Any timesheet/billing
> application will have to deal with the sort of problem you're
> describing, and it makes sense to learn what approaches others have tried.
thanx, I was already told that a view gets calulated every time. in
this case I'll write the logic in java (seems to be a nice chalange for a
beginer :) ).

Bye
--
Haim