Design for Vacation Tracking System
- by Aaronaught
I have been tasked with developing a system for tracking our company's paid time-off (vacation, sick days, etc.)
At the moment we are using an Excel spreadsheet on a shared network drive, and it works pretty well, but we are concerned that we won't be able to "trust" employees forever and sometimes we run into locking issues when two people try to open the spreadsheet at once. So we are trying to build something a little more robust.
I would like some input on this design in terms of maintainability, scalability, extensibility, etc.
It's a pretty simple workflow we need to represent right now:
I started with a basic MS Access schema like this:
Employees (EmpID int, EmpName varchar(50), AllowedDays int)
Vacations (VacationID int, EmpID int, BeginDate datetime, EndDate datetime)
But we don't want to spend a lot of time building a schema and database like this and have to change it later, so I think I am going to go with something that will be easier to expand through configuration. Right now the vacation table has this schema:
Vacations (VacationID int, PropName varchar(50), PropValue varchar(50))
And the table will be populated with data like this:
VacationID | PropName | PropValue
-----------+--------------+------------------
1 | EmpID | 4
1 | EmpName | James Jones
1 | Reason | Vacation
1 | BeginDate | 2/24/2010
1 | EndDate | 2/30/2010
1 | Destination | Spectate Swamp
2 | ... | ...
I think this is a pretty good, extensible design, we can easily add new properties to the vacation like the destination or maybe approval status, etc. I wasn't too sure how to go about managing the database of valid properties, I thought of putting them in a separate PropNames table but it gets complicated to manage all the different data types and people say that you shouldn't put CLR type names into a SQL database, so I decided to use XML instead, here is the schema:
<VacationProperties>
<PropertyNames>EmpID,EmpName,Reason,BeginDate,EndDate,Destination</PropertyNames>
<PropertyTypes>System.Int32,System.String,System.String,System.DateTime,System.DateTime,System.String</PropertyTypes>
<PropertiesRequired>true,true,false,true,true,false</PropertiesRequired>
</VacationProperties>
I might need more fields than that, I'm not completely sure. I'm parsing the XML like this (would like some feedback on the parsing code):
string xml = File.ReadAllText("properties.xml");
Match m = Regex.Match(xml, "<(PropertyNames)>(.*?)</PropertyNames>";
string[] pn = m.Value.Split(',');
// do the same for PropertyTypes, PropertiesRequired
Then I use the following code to persist configuration changes to the database:
string sql = "DROP TABLE VacationProperties";
sql = sql + " CREATE TABLE VacationProperties ";
sql = sql + "(PropertyName varchar(100), PropertyType varchar(100) ";
sql = sql + "IsRequired varchar(100))";
for (int i = 0; i < pn.Length; i++)
{
sql = sql + " INSERT VacationProperties VALUES (" +
pn[i] + "," + pt[i] + "," + pv[i] + ")";
}
// GlobalConnection is a singleton
new SqlCommand(sql, GlobalConnection.Instance).ExecuteReader();
So far so good, but after a few days of this I then realized that a lot of this was just a more specific kind of a generic workflow which could be further abstracted, and instead of writing all of this boilerplate plumbing code I could just come up with a workflow and plug it into a workflow engine like Windows Workflow Foundation and have the users configure it:
In order to support routing these configurations throw the workflow system, it seemed natural to implement generic XML Web Services for this instead of just using an XML file as above. I've used this code to implement the Web Services:
public class VacationConfigurationService : WebService
{
[WebMethod]
public void UpdateConfiguration(string xml)
{
// Above code goes here
}
}
Which was pretty easy, although I'm still working on a way to validate that XML against some kind of schema as there's no error-checking yet. I also created a few different services for other operations like VacationSubmissionService, VacationReportService, VacationDataService, VacationAuthenticationService, etc. The whole Service Oriented Architecture looks like this:
And because the workflow itself might change, I have been working on a way to integrate the WF workflow system with MS Visio, which everybody at the office already knows how to use so they could make changes pretty easily. We have a diagram that looks like the following (it's kind of hard to read but the main items are Activities, Authenticators, Validators, Transformers, Processors, and Data Connections, they're all analogous to the services in the SOA diagram above).
The requirements for this system are:
(Note - I don't control these, they were given to me by management)
Main workflow must interface with Excel spreadsheet, probably through VBA macros (to ease the transition to the new system)
Alerts should integrate with MS Outlook, Lotus Notes, and SMS (text messages). We also want to interface it with the company Voice Mail system but that is not a "hard" requirement.
Performance requirements: Must handle 250,000 Transactions Per Second
Should be able to handle up to 20,000 employees (right now we have 3)
99.99% uptime ("four nines") expected
Must be secure against outside hacking, but users cannot be required to enter a username/password.
Platforms: Must support Windows XP/Vista/7, Linux, iPhone, Blackberry, DOS 2.0, VAX, IRIX, PDP-11, Apple IIc.
Time to complete: 6 to 8 weeks.
My questions are:
Is this a good design for the system so far?
Am I using all of the recommended best practices for these technologies?
How do I integrate the Visio diagram above with the Windows Workflow Foundation to call the ConfigurationService and persist workflow changes?
Am I missing any important components?
Will this be extensible enough to support any scenario via end-user configuration?
Will the system scale to the above performance requirements?
Will we need any expensive hardware to run it?
Are there any "gotchas" I should know about with respect to cross-platform compatibility? For example would it be difficult to convert this to an iPhone app?
How long would you expect this to take? (We've dedicated 1 week for testing so I'm thinking maybe 5 weeks?)
