Re: OO versus RDB

From: H. S. Lahman <>
Date: Thu, 29 Jun 2006 16:33:20 GMT
Message-ID: <khTog.3926$H32.1871_at_trndny06>

Responding to Frebe73...

>>* employeeNo
>>+ salary

>>where 'salary' is semantically defined as base salary. Now suppose it
>>becomes necessary to keep track of burdened salary. For whatever
>>reasons the DBA decides to deliberately denormalize and use a separate
>>attribute that depends on 'salary' and other attributes of
>>[SalariedEmployee]. (With appropriate stored procedures to synchronize
>>the value with changes to those attributes.) The DBA then proceeds to
>>"clean up" the schema:
>>* employeeNo
>>+ baseSalary
>>+ burdenedSalary
>>The semantics of 'baseSalary' is exactly the same as the original
>>'salary' so an application needing the base salary still uses exactly
>>the same value. But it can't get it without rewriting the query to use
>>the new field name. However, the application solution doesn't care
>>about what the schema name is or even where it comes from. IOW, the
>>application solution is completely unaffected by the schema change --
>>provided one encapsulates RDB access in a subsystem and changes the
>>query there.

> If the semantics of the old column "salary" are the same as the new
> column "baseSalary", why on earth would you need to change the name? If
> the name is not changed, no existing SQL statements would break. Only
> the applications that needs the new "burdenedSalary", would need to be
> rewritten.

To capture the correct semantics of the attribute. In a context where one must distinguish between different flavors of salary, the original name was poorly chosen and the DBA fixed that. The attribute name is supposed to reflect the attribute semantics. Not changing it just leads to opportunities for new applications to screw up later.

However, that isn't the point. There are lots of ways to modify schemas without affecting individual attribute semantics. For example, I can easily construct an example where the attribute is simply moved to another table (e.g., to a new [EmployeeSalaryInfo] table, which is a child of [Employee]). The application query would still be broken.

> In what way was the schema denormalized? What did the stored procedure
> do?

As stated, the burdenedSalary attribute's value is dependent on other non-key attributes of Employee. That violates 3NF, which requires that non-key attributes be dependent only on the tuple key.

The stored procedure ensures that burdenedSalary is updated whenever any of the attributes it depends upon is modified. That is necessary for dependent attributes to ensure data integrity outside the scope of the update transaction.

>>This sort of situation is actually rather common at the enterprise

> Changing names of columns but keeping the sematics is extremly rare,
> just because it breaks the interface to the applications.

So do all changes to enterprise schemas, which is why /any/ change to such a schema is a big deal. The DBA already had to change the schema to provide burdenedSalary and chose that opportunity to clean up the semantics.

>>That's because different applications use the same data for
>>different reasons.

> But no single application can force the database schema to break. The
> applications might use the data for different reasons, but the data is
> still the same.

You are pulling a statement out of context and arguing a different point. I said nothing about applications forcing schemas to break.

>>When requirements changes trigger changes to the
>>schema, those requirements typically only affect some of the reasons the
>>applications use the data. So only a fraction of the applications that
>>use the data are affected by the change and have to be modified for the
>>requirements changes.

> In your example, only the applications that need the new column would
> need to change. All other remains unchanged.

No, in my example any application that accesses 'salary' must have the query construction code modified even though they don't access the new attribute.

There is nothing wrong with me that could not be cured by a capful of Drano.

H. S. Lahman
Pathfinder Solutions -- Put MDA to Work
blog: Pathfinder is hiring: (888)OOA-PATH Received on Thu Jun 29 2006 - 18:33:20 CEST

Original text of this message