I inherited an application that associates many different types of activities with a site. There are roughly 100 different activity types, and each one has differing set of 3-10 fields. However, all activities have at least one date field (could be any combination of date, start date, end date, scheduled start date, etc.), and one responsible person field. All other fields vary widely and a start date field will not necessarily be called "Start Date".
Making one subtype table for each activity type would result in a schema with 100 different subtype tables, which would be too unwieldly to deal with. The current solution to this problem is to store the activity values as key-value pairs. This is a greatly simplified schema of the current system to get the point across.
Each Activity has multiple ActivityFields; each Site has multiple Activities, and the SiteActivityData table stores the KVPs for each SiteActivity.
This makes the (web-based) application very easy to code because all you really need to do is loop over the records in SiteActivityData for a given activity and add a label and input control for each row to a form. But there are lots of problems:
- Integrity is bad; it's possible to put a field in SiteActivityData that does not belong to the activity type, and DataValue is a varchar field so numbers and dates need to be constantly cast.
- Reporting and ad-hoc querying of this data is difficult, error prone, and slow. For example, getting a list of all activities of a certain type that have an End Date within a specified range requires pivots and casting varchars to dates. The report writers HATE this schema, and I don't blame them.
So what I am looking for is a way to store a large number of activities that have almost no fields in common in a way that makes reporting easier. What I have come up with so far is to use XML to store the activity data in a pseudo-noSQL format:
The Activity table would contain the XSD for each activity, eliminating the need for the ActivityField table. SiteActivity would contain the key-value XML so each activity for a site would now be in a single row.
An activity would look something like this (but I haven't fleshed it out fully):
<SomeActivityType> <SomeDateField type="StartDate">2000-01-01</SomeDateField> <AnotherDateField type="EndDate">2011-01-01</AnotherDateField> <EmployeeId type="ResponsiblePerson">1234</EmployeeId> <SomeTextField>blah blah</SomeTextField> ...
- The XSD would validate the XML, catching errors like putting a string in a number field at the database level, something that was impossible with the old schema that stored everything in varchar.
- The recordset of KVPs that is used to build the web forms could easily be reproduced using
select ... from ActivityXML.nodes('/SomeActivityType/*') as T(r)
- An xpath subquery of the XML could be used to produce a result set that has columns for start date, end date, etc without using a pivot, something like
select ActivityXML.value('.[@type=StartDate]', 'datetime') as StartDate, ActivityXML.value('.[@type=EndDate]', 'datetime') as EndDate from SiteActivity where...
Does this seem like a good idea? I can't think of other ways to store such a large number of differing sets of properties. Another thought I had was keep the existing schema and translate it into something more easily queryable in a data warehouse, but I have never designed a star schema before and would have no idea where to begin.
Additional question: If I define a tag as having a date data type in the XSD using
xs:date, is SQL Server going to index it as a date value? I'm concerned if I query by date it will need to cast the date string to a date value and blow any chance of using an index.