April 21, 2015 at 12:09 pm
I am trying to figure out the best database design for the following;
I have individuals (stored in Agents table) that enter their billing or time worked each day for different projects. Now we have different rates that we pay based on the person and what the project is. And we also have to account for individuals getting pay cuts or raises over time. So....
I have the following tables;
Agents - table of each person
Projects - table of all projects we work
Rates - table with AgentID, ProjectID, StartDate, EndDate, Rate
Billing - table with Date, AgentID, ProjectID
So my current implementation a billing entry must be made by an agent, and I have to create a join to the rates table based on AgentID, ProjectID, and Date between StartDate and EndDate. This just seems incredibly ugly and all the billing data can be easily changed if a change were made to the rates. I'm assuming most time sheets calculate the total rate and store it in the table so it never changes unless someone specifically goes back to update the data? I'm just wondering if there is a better method for tracking this sort of data. Thanks
April 21, 2015 at 1:42 pm
Hello and welcome to SSC. I am not clear about what the purpose of the billing table is again. How does this table get updated and what's it's purpose?
Just so we have something to start with I'm guessing you have something like this:
CREATE TABLE dbo.Agents
(
AgentID int NOT NULL,
AgentFirstName varchar(50) NOT NULL,
AgentLastName varchar(50) NOT NULL,
CONSTRAINT pk_agents PRIMARY KEY (AgentID) -- perhaps this could be non-clustered with a clusterd index created later (depending on your queries)
);
GO
CREATE TABLE dbo.Projects
(
ProjectID int NOT NULL,
ProjectDesc varchar(100) NOT NULL DEFAULT(''),
CONSTRAINT pk_Projects PRIMARY KEY (ProjectID) -- perhaps this could be non-clustered with a clusterd index created later (depending on your queries)
);
GO
CREATE TABLE dbo.Rates
(
AgentID int NOT NULL,
ProjectID int NOT NULL,
StartDate date NOT NULL,
EndDate date NULL, -- NULLable for now, should make NOT NULL later and come up with a way to handle this
Rate money NOT NULL DEFAULT(0),
CONSTRAINT pk_rates PRIMARY KEY (AgentID, ProjectID),
CONSTRAINT fk_AgentID FOREIGN KEY (AgentID) REFERENCES dbo.Agents(AgentID),
CONSTRAINT fk_ProjectID FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID)
);
GO
-- Always index your foriegn key columns
CREATE NONCLUSTERED INDEX nc_rates_agentID ON dbo.Rates(AgentID);
CREATE NONCLUSTERED INDEX nc_rates_ProjectID ON dbo.Rates(ProjectID);
GO
CREATE TABLE dbo.Billing
(
AgentID int NOT NULL,
ProjectID int NOT NULL,
BillingDate date NOT NULL,
CONSTRAINT pk_billing PRIMARY KEY (AgentID, ProjectID, BillingDate) -- We'll assume only 1 Agent/Project entry per day
);
GO
-- Itzik Ben-Gan 2001
April 21, 2015 at 1:49 pm
Yes, the sql you've posted is the basic design I am currently using.
The purpose of the billing table is to capture all the work performed by the different agents. This table is how we handle payroll. So if User John Smith entered 2 days of billing on project WORK, the billing table would have two entries (one for each day) and we would be able to create a join on the rates table (again the ugly join with AgentID, ProjectID, and Date between StartDate and EndDate) to be able to see how much money we owe him.
April 22, 2015 at 3:28 am
I would be looking to further separate the entities:
Work done (agent, Project, activity type) is separate from the work value (agent, activity type, rate, rate_start, rate_end)
If the rate for an activity can depend on the project type (e.g. commerical, public sector, charity) or project status (e.g. better rate for overtime, lower rate if project is in rework overruns) then I would also be looking to separate the rate card from the activity type
Project (project ID, Project Type)
work done (agent, projectID, activity type)
RateCard (project Type, Activity type, Rate Code)
work value (agent, projectID, ratecard)
Rate (ratecard, rate, rate_start, rate_end)
This makes the joins even more complicated but provides greater flexibility, even if you don't need it just yet.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply