Bitemporal Database Design Question RRS feed

  • Question

  • I am designing a database that needs to store transaction time and valid time, and I am struggling with how to effectively store the data and whether or not to fully time-normalize attributes. For instance I have a table Client that has the following attributes: ID, Name, ClientType (e.g. corporation), RelationshipType (e.g. client, prospect), RelationshipStatus (e.g. Active, Inactive, Closed). ClientType, RelationshipType, and RelationshipStatus are time varying fields.  Performance is a concern as this information will link to large datasets from legacy systems.  At the same time the database structure needs to be easily maintainable and modifiable. Any thoughts?

    Monday, August 15, 2011 3:32 PM

All replies

  • LKNikko,

    So you are saying you need to record the exact date and time that ClientType, RelationshipType, and RelationshipStatus are updated?

    If so it sounds like you are going to need to create an update trigger that captures when one of those fields is updated and records the current date and time either in another column in your table or creates a transaction in a log table. 

    Tom Overton
    Monday, August 15, 2011 4:31 PM
  • I forgot to mention, you will probably also need to have a reference table that defines the time span for that particular record and column.  So maybe you need another table that has:

    ID (foreign key to Client table), TimeBegin, TimeEnd, FieldType (something that tells you that this record relates to RelationshipType, RelationshipStatus, or ClientType). 

    Is this at least kind of close to what you're trying to do? 

    Tom Overton
    Monday, August 15, 2011 4:41 PM
  • Yes, I need to capture both the transactional time and the valid time, so that's four date fields. I am planning on splitting out audit trail and point-in-time history into separate tables.  I'm really more concerned with the database architecture rather than how to grab the dates themselves.

    Monday, August 15, 2011 4:43 PM
  • To be more specific, I am struggling with whether or not to break the time varying attributes into one or more separate tables.

    1)      Three tables: Client, ClientHist, and ClientAudit. Client will contain the current state. ClientHist will contain any previously valid states, and ClientAudit will be for auditing purposes. For ease of discussion, let’s forget about ClientAudit and assume the user never makes a data entry mistake. Doing it this way, I have two ways I can update the data. First, I could always require the user to provide an effective date and save a record out to ClientHist, which would result in a record being written to ClientHist each time a field is changed. Alternatively, I could only require the user to provide an effective date when one of the time varying attributes (i.e. ClientType, RelationshipType, RelationshipStatus) changes. This would result in a record being written to ClientHist only when a time varying attribute is changed.

    2)      I could split out the time varying attributes into one or more tables.  If I go this route, do I put all three in one table or create two tables (one for RelationshipType and RelationshipStatus and one for ClientType).  Creating multiple tables for time varying attributes does significantly increase the complexity of the database design.

    Tuesday, August 16, 2011 2:30 PM