question

Mark Eckroth avatar image
Mark Eckroth asked

Hierarchical, date-related configuration

I was looking for ideas on how to design a database so that reports could use time-sensitive lookup information. Our production and quality configurations change regularly; an employee changes departments, a department gets a new supervisor, a task is moved from one process to another, a product's attribute must meet a higher standard, an attribute is checked during a different inspection. I would like a scheme where the production and quality data is matched to the configuration information based on the date of the production or inspection. Every other application that I've seen implement an idea like this the current configuration data is simply written into the data; so instead of a record that says employee #12 produced 100 pieces during task #23 on 12/1/09, the 'flat' record includes the names of the supervisor and department at the time.

I have a working design but it is difficult to use and a bit slow.

auditlookups
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

I'm not sure if this is an answer to your question or not, the requirements are a bit vague, but we did a design where we created a VersionId for updates and stored the value on all tables. It was monotonically increasing, so each update set could be identified and each update set was greater than the last one. Further, with the inclusion of effective dates we were not only able to see what the current state was, but to apply "new" versions with backdated effective dates, overriding previous entries. With this mechanism, you can always get the latest sets of data by selecting TOP(1) VersionID that is <= the latest version.

I'm only talking here in general terms because, again, I'm not entirely sure what you're looking for.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered

As Grant Fritchey already said there, you can add a version field to the tables and maintain table with version ID's with the version validity dates/times.

Also you can add a ValidTo and/or ValidFrom fields to the tables which needs to contain a time depended values. You can have only one or both of the Date/Time fields in the table, depending on type fo data and requirements.

For easy access to that data, instead of using selects to that tables, you can simply create inline table-valued functions with datetime parameter to retrieve values valid at particular date. So instead using direct selects to the tables you will do selects to a table-valued functions with apropriate parameter.

If the table-valued functions are Inline Table-valued functions, then SQL query optimizer takes them as views/selects and optimizes futher queries using them with additional contitions, joins etc.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

We needed something like this on a previous system I worked on, but some tables were very large (millions of rows) even without versioning. In order to provide the required functionality but still maintain high performance for typical queries and updates (which only needed the most recent data), we created version tables for each table that needed one. As an example (albeit a rather silly one), let's consider a table containing a person's ID, the date and what clothes they are wearing that day. If most users only care about what each person is wearing today, but you sometimes need to check historical values, you could set up a structure like this:

CREATE TABLE employee_clothing
(
    employee_id int,
    clothing_desc varchar(2000)
)

CREATE TABLE employee_clothing_versions
(
    employee_id int,
    dt DATETIME,
    clothing_desc varchar(2000)
)

An update trigger can then be used to copy previous data to the versions table after new data is inserted in the main table for a given employee. This, of course, decreases performance for updates, but it keeps the main table smaller to maintain good performance for reads. Considering this example for 10000 employees, the main table would always have 10000 rows, but the versions table would have over 3.5 million rows in less than a year (if weekends are included).

I'm not sure how much this helps answer the original question (especially since another answer was already accepted), but I wanted to provide this answer anyway to possibly help others. It may seem a bit unusual to some, but it definitely helped us a lot in our real-world scenario. It's just another example of why it is so important to truly know your requirements before trying to meet or exceed them. If reading historical data or fast updates had been the most important thing, then this would have been a bad solution. But since reading current information was most important to us, this proved to be an excellent solution.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.