March 25, 2008 at 4:59 pm
Hi there,
I'm working on a web based application that will be collecting data for OSHA/Worker's Compensation forms. We're starting with Canadian Worker's Compensation forms, but will be doing OSHA forms soon thereafter.
Each state/province has a number of their own unique forms (i.e. Alberta Employee Injury/Disease, Ontario Needlestick/Body Fluid). These forms can also differ each year.
How can I best model the data? For example, should I have WCB_2006CAAB_EmployeeInjuryDisease_Info and WCB_2007CAON_NeedlestickBodyFluid_Info tables (where the format is WCB_ )
This would result in a large amount of tables (especially if I do get around to all the forms for all the provice/states). But as the data is unique to each country, province, year and form I can't see another way.
March 25, 2008 at 7:28 pm
Is there at least some commonality between the forms? For example, entry date, name of person filling out the form, key place, incident date.
Do you have any sense on how the tables will be queried? Would the most likely requests be like "give me all the info from form x with id number y" or "find all cases where whatever happened in industry ##### on Tuesdays"?
I do not know how this would scale or how you would carry it off, but maybe you could make a table that has a set of columns for relatively common, likely to be queried data; an xml column to hold every completed form with all of their weirdness; and a column that tells you which schema applies to the xml column. You have to put the schemas somewhere too, maybe in their own table. Since you are on a SQL7, 2000 forum the xml would go into a text column not a xml datatype column and running queries would be a challenge.
March 26, 2008 at 9:36 am
There is not a lot of commonality between the forms. So far, I've got a master table called WCB_Info that contains the common things like the claim number, employee involved, form id, organization, comments... But it's not much. I wish the provinces could all get together and have just one set of forms...
As for the queries, the most prominent ones I see at this point are "give me the form for claim number x", "give me all forms between date x and date y"
I hadn't thought of xml for the form data. We're using SQL 2000, so I could use an NTEXT. I'm concerned about using xml due to the query performance (I'm always weary of generic xml columns like that). I think it could work. It's probably a lot better than 50+ + 12+ tables per year.
I guess now the question is, how do I make my web forms, because they'll run into the same sort of issue. Maybe I can use the XML schema for directing the display forms.
March 26, 2008 at 5:48 pm
With the types of queries you mentioned you should be ok on performance since they do not require you to parse the xml. That is just an assumption on my part because I have not worked with xml on the scale you have.
As for the web pages, stylesheets and schemas would be key. You want to be rigorous with your naming conventions.
I hope you have time for trial and error on this and do not have to commit to a concrete plan in the next few days.
March 27, 2008 at 8:48 am
Thanks for your help.
I'm sure the schema's are going to be the majority of the task.
No I don't have to come up with a concrete plan very soon, we've kludged a couple forms that were necessary using individual tables. I'll start the conversion sometime in the next couple months.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply