Schema for lists of relationship differences

Question

Say there is a data model that allows for relationships to be defined either explicitly or as a list of records added or removed relative to some existing item: Item <–> *Properties and Item <- InheritedItem <-> *AddedProperties, *RemovedProperties, *ChangedProperties. How would such a model be best represented in a relational database?

Having thought about possible schemas, I've come up for two general options for storing the list of changes:

• Two or three different tables for added, removed, changed properties.
• A single table that has a change type flag and all the necessary columns for all three change types.

Regarding change tracking, there are again two options I've come up with so far:

• Storing changed properties as two foreign keys, one to the old property, one to the new property.
• Giving the Property table a nullable foreign key to the original property and recording the change as as an added and a removed property.

Clarification: This question relates to additions & deletions in the sense of version control (i.e. an object of this kind was replaced with a new object that has these changes relative to the parent object), not in the sense of object inheritance.

Answer 1

If I understand your question correctly you are looking to store, let's say, a Person entity with Name and DateOfBirth attributes. You will also have an Employee entity which is defined as everything a Person has plus EmploymentNumber plus DeskNumber but doesn't have DateOfBirth. I'm guessing you want to be able to add a column to a base type and automatically see it showing up in the sub-type(s), and sub-sub-type(s). In short you are looking for an object-oriented data store. RDBMSs in general are not will suited to deliver this featrure.

If you know what these differences are and they are stable, at least between schema relases, you can define a type/ sub-type model. The base table has the columns which are common to all types. It has a surrogate primary key. From there you define further tables, strictly adding columns as you go. The same ID is carried through this hierarchy. The case where you need to remove columns from one type to a descendant is handled by defining an abstract common ancestor and placing the removed column in one branch but not the other.

For my example above we would end up with these tables

Human
    ID     PrimaryKey
    Name

Person
    ID     Primary key and also foreign key to Human.ID
    DateOfBirth

Employee
    ID     Primary key and also foreign key to Human.ID
    EmploymentNumber
    DeskNumber

You could add an column to the base table (Human) to indicate which of the sub-types any particular occurance writes to. I don't think that is necessary because the process which writes to this database has to know which type it is dealing with in order to capture the correct values. Even if that process dynamically builds its list of attributes by examining the DB, it has to know which ultimate sub-type it is looking for to bootstrap the process.

It is tempting to have, say, EmployeeID and a separate foreign key HumanID. This is unnecessary.

When retrieving values you will either be interested in a known occurrance of a sub-type or will be looking for all available values for an instance i.e. "Human 99 as Employee" or "All about occurrance 99". The former can be had by INNER JOINing Employee up to its ultimate ancestor using the defined keys. The latter by OUTER JOINing all tables in the model.

If your attributes can vary at run time you will be forced into using some variant of an entity-attribute-value (EAV) model. This has been well documented in many, many blogs, papers, forums and SO questions. While being fractious, conceptually challenging and non-performant these models can be made to work (for an appropriate definiton of "work"). Your tables will be a lot like this:

ItemType
    ItemTypeID    primary key
    Name
    ParentTypeID  fk to ItemTypeID

Attribute
    AttributeID   primarykey
    Name
    AddedOrDeletedInd
    ItemTypeID    fk to ItemTypeID

Item
    ItemID
    ItemTypeID    fk to ItemTypeID
    ParentItemID  fk to ItemID

ItemValue
    AttributeID   fk to Attribute
    ItemID        fk to Item
    Value

ItemValue.Value could be a catch-all varchar() column or you could have one for each datatype you want i.e. ValueInt, ValueDate, ValueChar etc. and and indicator in Attribute to say which is populated. As you can see reading any one item's full definition will require recursion through a tree. Good luck.

Answer 2

There are a couple of ways. You can simply create a log table that has columns like this:

customer_log_id, customer_id, old_property1, new_property1, old_property2, new_property2, etc...

or just save the "new" values and store them in order -- playing the log in reverse to undo changes.

You can do a smaller and I contend more simple setup by using json or hstore fields.

customer_log, customer_id, old_json, new_json, date

You can even use triggers to populate the fields, particularly if there aren't a lot of non-standard datatypes in the tables you're trying to log. One other way is to do the longest log.. which is a record for each attribute that changes.