The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Master-Detail Relationship, with a twist

Imagine a master-detail relationship between two tables, enforced by a numeric Primary Key / Foreign Key pair.

In addition, the master table has an "Effective Date" column, and the detail table has a "Date" column.

The desired rule to enforce is that, for each detail record, its FK to the master table must "agree" with the date columns.  To agree, the detail date must be equal to or greater than its parent record's effective date, but must also be strictly less than the *next* effective date.

I know this is not common, and I'm open to alternative ways to store the same data, but the end user *does* need to *think* in terms of the detail records falling within a master Effective Date range, and the user should *not* have to re-assign detail records manually whenever new records are created in either table, nor when either date column is edited.

Has anyone else needed to enforce such a rule before?  Did you use a different table structure?  Did you use a strucutre like this, and how can you enforce the rule, especially when multiple users are hitting these tables.  An added complication is that our users need to be able to mess with these records in an in-memory cache, so another user's INSERTs may not be available just yet.
I hate effective dates
Monday, January 09, 2006
 
 
Surrogate keys?

If you can calculate an unique surrogate key from the master date range, you can use that to link the detail.
Sevenoaks Send private email
Monday, January 09, 2006
 
 
I don't think you are expressing the problem clearly.

Are you wanting to use the date field as a key field, or use synthetic keys?

"must also be strictly less than the *next* effective date."

What does "next" mean?

"the user should *not* have to re-assign detail records manually whenever new records are created in either table, nor when either date column is edited."

This makes no sense to me.

Google "check constraints".  But I think the logic you want is too involved and you will have to handle it in application code rather than delegate to the database, especially if you want to factor in uncommitted data.
dave
Monday, January 09, 2006
 
 
My bad.  Let me get more specific.  There are devices on which we track repairs.  In addition to repairs, some qualities of the device will change once in a blue moon, let's say that it's color might change every other year or so, and it might get repairs every couple of months.

Table Device (
DeviceID Guid PRIMARY KEY,
Name VARCHAR(25)
);

Table DeviceHistory (
DeviceHistoryID Guid PRIMARY KEY,
DeviceID Guid, -- This is a foreign key.
EffectiveDate DateTime,
Color VARCHAR(10)
);

Table DeviceRepairs (
DeviceHistoryID Guid -- This is a foreign key.
RepairDate DateTime
);

If you look at all of the history records for a single device, you get a series of effective dates.  For a single history record, you can have a few repair records.  But, once you have some data in all these tables, you might edit the effective date of one record, which means you'll want to update some of the foreign keys in the repairs table.

At all times, the RepairDate must be >= its corresponding EffectiveDate, and it must be < the next smallest EffectiveDate for that device.






Are you wanting to use the date field as a key field, or use synthetic keys?

"must also be strictly less than the *next* effective date."

What does "next" mean?

"the user should *not* have to re-assign detail records manually whenever new records are created in either table, nor when either date column is edited."

This makes no sense to me.

Google "check constraints".  But I think the logic you want is too involved and you will have to handle it in application code rather than delegate to the database, especially if you want to factor in uncommitted data.
I hate effective dates
Monday, January 09, 2006
 
 
How is the next effective date determined?

It sounds like you're assuming that repairs must fall within a consistent window; for example certain activities must be performed within each quarter.  Err...  something like this example:

I need to calculate payroll taxes each quarter.  I can submit them any time during the following quarter, but I must do so before that following quarter ends.  1st quarter taxes are due before the end of the 2nd quarter, but I can submit them as many times I want during that time frame.

That about right?

If the window is consistent and easily described (every 30 days), you can do it as a constraint.

Alternatively, you can define both a starting effective date and an ending effective date in the device history table, and simply impose a double constraint.

Last option is to verify that the repair date value is valid at the application level - which allows you the maximum flexibility in defining the rule.
Anonymous Coward
Monday, January 09, 2006
 
 
Does your database have triggers? They were made to capture complicated logic within the database.

I hate moving stuff out of the database, but I'm funny that way.
Steve Hirsch Send private email
Monday, January 09, 2006
 
 
Check constraints cannot reference other tables or other records in the same table. It would be a straightforward trigger to write in Oracle or Postgres. SQLServer, dunno.
George Jansen Send private email
Tuesday, January 10, 2006
 
 
The effective date values are basically random.  The user thinks "I changed quality X of the device today, and I want the new state associated with every repair going forward, and I don't want to forget what the state of the device was for previous repairs.

I think that I'm going to try to change the foreign key relationships.  Right now, as in my previous message, there is a parent, child, grandchild setup.  Device will have two children: DeviceHistory and DeviceRepairs.  The dates only need to match up for presentation purposes, so I'll select a few columns from all 3 tables, using INNER JOINs along the relationships, and a (fairly ugly) date comparison in the WHERE clause.  I'm a little worried it'll be slow, but I'll cross that bridge when I get to it.
I hate effective dates
Tuesday, January 10, 2006
 
 
DeviceRepairs should have a foreign key to Device instead of to DeviceHistory.

The DeviceHistory to which the repair was done can be determined from the RepairDate and the EffectiveDate.

Now, whenever the EffectiveDate changes, the DeviceRepairs automatically refer to the correct DeviceHistory.
Geert Baeyaert
Tuesday, January 10, 2006
 
 
+1 to Geert

Both DeviceHistory and DeviceRepairs should link directly to Device. You'll need to think about constraints on EffectiveDate in DeviceHistory to make sure any changes here make sense, but this should be easy to manage.
Keep it simple
Tuesday, January 10, 2006
 
 
The way to think about it is that your users are making repairs to a device, not to a device history! They are, in addition, keeping a *separate* history of the 'state' of the device as it is modified over time. The two are *separate* concepts.
Keep it simple
Tuesday, January 10, 2006
 
 
Many thanks.  I see now how Geert's description of the relationships is correct, and that matching up dates is a presentation concern rather than a data model concern.
I hate effective dates
Tuesday, January 10, 2006
 
 
I didn't read the problem carefully, but Geert's response is much better than mine. I have found that when confronted with such a difficult problem, the problem usually lies in the design.
Steve Hirsch Send private email
Wednesday, January 11, 2006
 
 
You never say what qualifies a device as effective.  Moreover, I'm having trouble with the simple case of a single device's repair history and effective date history.

Device A is a blue drill.  It is effective, which I gather means "available for use", since 1/1/2005.

You repair it on 4/1/2005.  Was it taken out of service, i.e. make ineffective, at some point?  Maybe you made it ineffective 3/30/2005.  You repaired it and got it back into rotation 4/1/2005.  Now we have some information like

Device A effective 1/1/2005 - 3/30/2005
Device A effective 4/1/2005 - 1/12/2006

Is this basically it?  You want to track downtime?  If so, you only need two tables, not three.  At least, that's how the top maintenance management software data models tend to do it.  The third is really frosting, because you can derive downtime with a SQL query.

Go buy this <ISBN: 0123693799> and read up on how this and related problems are solved.

-C
Christopher Send private email
Thursday, January 12, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz