"Resource" management design

Question

"Resource" management design

Alexis Georges

SSC Journeyman

Points: 88
More actions
May 12, 2008 at 9:16 am

#182236

I have service types, employees and freelancers.
A service is specified by a type, an employee/freelancer and either a language-pair or a single language. Now my problem is that only services associated to freelancers have a "cost" (and a unit for that cost), so right now I have as many tables as service types, times 2, one set for employees without cost and one for freelancers with a cost. The other problem is that some service types require that a service be defined by more than just an employee/freelancer and lang-pair/lang; for example one service type may require in addition a software.
Also the "cost" has different units depending on the service type (eg. per hour, per word, per page).
I have about 2 stacks of paper with ER diagrams all over them, and yet I cannot come up with a sound design.
I didn't specify the entire problem the design is trying to solve, but just the parts I am stuck on.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 1

Why did you specify one table per service type instead of 1 table to hold all service types? And if employees suffer no cost, why did you make a separate set of tables for them instead of just ignoring the cost in the other table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alexis Georges SSC Journeyman Points: 88 More actions · Answer 2

It's because some services relate differently to the resources. For example, Translation is in a ternary relation with the Employee/Freelancer and LanguagePair; the resulting relation has the cost attribute; but for Employees there should be not cost attribute, so I have 2 tables instead of 1 so that I do not have NULL values (on rows where the resource represents an Employee, who should have no cost)

Now, DTP is in a quaternary (4-ary) relation with Employee/Freelancer, Language (not pair) and Software; the resulting relation gives the cost attribute, but again, I have 2 separate relations, one with a cost attribute for Freelancers and another for Employee with not cost attribute.

So I don't see how I could have one single table with all services, since some services relate to the resources differently.

Maybe I am looking at this the wrong way though. Did that answer your questions?

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 3

Why not just ignore the cost for Employees since they suffer no cost? That would cut the number of tables in half and also keep you from having to keep dual masters.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)