CODEWITHSUNDEEP
relational-database
P a u l
P a u l

Reputation: 7911

Relational tables design problem

How do you retain historical relational data if rows are changed? In this example, users are allowed to edit the rows in the Property table at any time. Tests can have any number of properties. If they edit the field 'Name' in the Property table, or drop a row in the Property table, Test rows might not hold conditions at the time of the test. Would you change the design of the Test table by adding a property names column, and dropping the TestProperty mapping table? The property names column would have to be something like a delimited list of strings. How is problem usually handled?

3 tables:

Test:
  TestId     AUTONUMBER, 
  Name       CHAR,
  TestDate   DATE

Property:
  PropertyId AUTONUMBER, 
  Name       CHAR

TestProperty:  (maps properties to tests)
  TestId
  PropertyId

Upvotes: 1

Views: 219

Answers (3)

PerformanceDBA
PerformanceDBA

Reputation: 33708

I do not think the question has been answered fully.

If they edit the field 'Name' in the Property table ... Would you change the design of the Test table by adding a property names column, and dropping the TestProperty mapping table?

Definitely not. That would add massive duplication for no purpose.

If your requirement is to maintain the integrity of the data values (in Property) at the time of the Test, the correct (database) method is to implement a History table. That should be an exact copy of the source table, plus one item: a TIMESTAMP or DATETIME column is added to the PK.

PropertyHistory
    PropertyId  AUTONUMBER,
    Name        CHAR
    CONSTRAINT  PRIMARY KEY CLUSTERED UC_PK (PropertyId)

PropertyHistory
    PropertyId  INT,
    AuditedDtm  DATETIME,
    Name        CHAR
    CONSTRAINT PRIMARY KEY CLUSTERED UC_PK (PropertyId, AuditedDtm)
For this to be meaningful and useable, the Test table needs a timestamp as well, to identify which version of ProperyHistory to reference:
TestProperty
    TestId
    PropertyId
    TestDtm     DATETIME

The property names column would have to be something like a delimited list of strings.

That would break basic design rules as well as Database Normalisation rules, and prevent you from performing ordinary Relational operations on it. Never store more than one data value in a single column.

... or drop a row in the Property table

Deletion is something different again. If it is a "database" then it has Integrity. Therfore you cannot delete a parent row if it has child rows in some other table (and you can delete it if it does not have children). This is usually implemented as a "soft delete", an Indicator such as IsObsolete is added. This is referenced in the various SELECTS to exclude the row from being used (to add new children) but remains available as the parent for existing children.

Upvotes: 3

John
John

Reputation: 16007

Seems like you're using Test as both a template for a particular instance of a test, as well as the test itself. Maybe every time a user performs a test according to the specification in Test, create a row in, say, TestRun? This would preserve the particular Propertys, and if the entries in Property change later, then subsequent TestRuns would reflect the new changes.

Upvotes: 1

Lee Louviere
Lee Louviere

Reputation: 5262

If you want to retain property relations, even if the property doesn't exist. Make it so that Properties aren't necessarily deleted, but add a flag that denotes if the property is currently active. If a property's name is changed, create a new property with the new name and set the old property to inactive.

If you do this, you'll have to create some way of garbage collecting the inactive properties.

I'd never make a single column into a field that imitates a one-to-multi relationship with a comma-denoted list. Otherwise, you defeat the purpose of relational database.

Upvotes: 1

Related Questions