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?)
Many thanks for your advices,
Aaron
