November 4, 2011 at 6:57 am
Hi folks,
I have a questions that keeps popiing up in my head every so often.
Say I have 3 tables one for tblSstaff, one for tblClients and one for keeping a record of and paying how many hours each member of staff works with each client tblWorkedHours its a common setup in a lot of organisations.
tblStaff , tblClient tblWorkedHours so far so good.
But on some ocassions staff don't work with a client, but still need paid for their time ie they may be on paid leave, training or some other activity.
So how do I create the ref integrity and where do i store the 'other 'activites ?
I think in the past we stored the small no of other activites within the client tables!
Any help appreciated.
November 4, 2011 at 7:01 am
Can you not just leave the client column NULL in tblWorkedHours where there is no client involved?
John
November 4, 2011 at 7:18 am
Define your own organization in the clients table. When staff are working on internal jobs the client is yourself. You should also have specific tasks defined in a Tasks table so the WorkedHours table would contain both clientID and a taskID.
The probability of survival is inversely proportional to the angle of arrival.
November 4, 2011 at 8:05 am
Hi John & sturner,
John's way is what I believe to be the standrard way, adding non client data to the tbl Client, but surely this breaks the rules of data integrity of the whole thing, its a kind of work around, but when I want a count of no. clients I have to remember to subtract the non client rows!
I dont like having null's as its too easy for the users to leave it null when they shouldn't.
The way I was thinking of was as sturner suggested, but have not yet tried it, as I can see a problem here, if you can you link the PK from both tblClients and tblTasks to a FK of tblWorkedHours, say CLientID for ref Integrity reasons(as it will be the client id most of the time), when you come to look at this field again how do you know if its a client ID or a Task ID?, what is both ID use the same numbering scheme?
There lies the rub !
I was thinking there must surely be a recommended solution to this thats works
Any ideas?
Is this good practice
November 4, 2011 at 8:15 am
Yes, you can have two foreign key constraints on tblWorkedHours - one referencing tblClients and one referencing tblTasks.
John
November 4, 2011 at 8:15 am
Tallboy (11/4/2011)
Hi,Thats they standrad way of ading non client data to the tbl Client surely this breaks the data integrity of the whoel thing, whne I wan a count of hoem any clients I have I have to remember to subtract the no client rows!
Yes, it is standard practice and very much consistent with relational rules to define your own organization in the clients table. What is wrong with using a WHERE clause to exclude your own internal clientID when you need to count up clients, or client hours?
I just don't see the issue, and don't understand what you are trying to accomplish with some sort of exotic referential integrity scheme.
The probability of survival is inversely proportional to the angle of arrival.
November 4, 2011 at 8:22 am
Tallboy (11/4/2011)
.... when you come to look at this field again how do you know if its a client ID or a Task ID?, what is both ID use the same numbering scheme?
This is why I stated you should have both a taskID and a clientID in your HoursWorked table. You should have a table containing all of your Clients (including your own organization if you have internal tasks to perform for it) and a Tasks table which defines as many tasks as you normally perform. Include a "non-categorized" taskID in there also for documenting any tasks that are not specifically defined.
Your referential integrity constraints merely enforce the relationships between the three tables and columns.
The probability of survival is inversely proportional to the angle of arrival.
November 4, 2011 at 8:25 am
Thats what I am asking is it normal practice.
There would be more than 1 task/organisation id to find as the task that the staff member is being paid for, this list of tasks could grow.
So each time you wanted a count of no of clients you have to remember to check if you have all the new task ID's and what they are to be able to remove them.
I suppose it's a prefect system i am looking for in which case there might not be one !
November 4, 2011 at 8:38 am
Tallboy (11/4/2011)
There would be more than 1 task/organisation id to find as the task that the staff member is being paid for, this list of tasks could grow.
Few things remain static forever. The tasks table would need to have defined tasks added to it periodically. Preferably you'd have an interface for adding/editing task definitions in it. I would have a "memo" or comments column in the HoursWorked table also so some specific details could be added when logging hours.
The probability of survival is inversely proportional to the angle of arrival.
November 4, 2011 at 9:06 am
Tallboy (11/4/2011)
Thats what I am asking is it normal practice.There would be more than 1 task/organisation id to find as the task that the staff member is being paid for, this list of tasks could grow.
So each time you wanted a count of no of clients you have to remember to check if you have all the new task ID's and what they are to be able to remove them.
No, you don't have to do that.
Either you count the entries in the client table (which are not affected by the new task table sturner suggested) and subtract 1 (because 1 clientID is the dummy client ID you use to cover work which isn't associated with an external client); or you count distinct client_ids in the hoursworked table and subtract 1.
Of course if you don't want to record what task the work was carrying out you don't need a task table or a taskid column in the hours worked table. If you do, you do need that extra table. But whether or not you have it the two possible methods of counting clients are the same, and tasks are irrelevant to counting clients.
So you can have a nice normalised system with no nullable columns and fully checked referential integriy constraints just by adding that single dummy client ID which represents your own organisation, as suggested by sturner. And yes, doing this is normal practise; but so, regrettably, is using NULL instead of a dummy client id for your own organisation, which says a lot about the sad state of db design practise in the world today; while I firmly believe that NULLs are useful and necessary (and wish SQL had got them right) I also firmly believe that designing a base table to use NULLs to represent a known fact rather than to indicate that some information is not available is an utterly silly thing to do.
Tom
November 4, 2011 at 9:14 am
L' Eomot Inversé (11/4/2011)
And yes, doing this is normal practise; but so, regrettably, is using NULL instead of a dummy client id for your own organisation, which says a lot about the sad state of db design practise in the world today; while I firmly believe that NULLs are useful and necessary (and wish SQL had got them right) I also firmly believe that designing a base table to use NULLs to represent a known fact rather than to indicate that some information is not available is an utterly silly thing to do.
Well stated. If I could have a dollar for how many times I have seen (or had to fix) database issues due to improper use of NULLs I could retire.
On the other hand, I have also had gainful employment over the years because of such poor design practices. 😎
The probability of survival is inversely proportional to the angle of arrival.
November 4, 2011 at 10:05 am
Hi L' Eomot Inversé,
I agree, whenever I see null entries in a table I think 'poor design!'
A null tells you nothing, take a simle example like tblGender , with Male or Female rows.
If the user is unaware of the clients gender they could leave it null but that might mean the user forgot/neglected/didnt know it, to put it in, Null tell you nothing.
I add TBA(To be advised) as an option as least this tell us the user did not know at the time and will complete it later !
This mantains the discipline for the user of having to make a choice and ref Integrity at the same time.
Going back to my example above it looks like I will have to add the task id's to the Client table to keep the whole ref integrity correct and simple.
Unless someone else has a better idea?
November 4, 2011 at 10:38 am
Tallboy (11/4/2011)
Going back to my example above it looks like I will have to add the task id's to the Client table to keep the whole ref integrity correct and simple.
Unless someone else has a better idea?
I believe you are missing the point. You don't need a taskID in the client table, you want a task table, and add a taskID to the hours worked table. That is how I would do it. Tasks and their descriptions are atomic entities and how many specific billable tasks can there be.... certainly not an infinite number by any stretch.
You might not perform all tasks for all clients but all task definitions should can reside in one table called tasks. Then all that is left is to place the taskID, the ClientID and the time and date worked into your HoursWorked table and you have everything properly normalized.
The probability of survival is inversely proportional to the angle of arrival.
November 7, 2011 at 6:22 pm
Sorry sturner,
perhaps i have not explained it enough, the task id and the client id must go into the same field in the hours worked table, because staff are either doing a task or working for a client!
November 7, 2011 at 6:34 pm
That would be NOT normal.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply