Re: Unknown SQL

A problem I've seen is that databases designed to support an object application are often not designed to support simple and relevant queries via the SQL interface (or any other relational style interface).

Two particular design constructs seem to surface over and over.

The first is to produce pairs of columns of the (Type, Value) variety. The values in the second column can be drawn from more than one domain (e.g. Social Security and zip code), and any needed disambiguation is provided by the type column.

 If the value is a foreign key, and references different tables depending on the value of type, the queries can get to be real complex, and real long running, real fast.

I never design relational data that way. I regard the rule that says that all values in a column are drawn from the same domain as fundamental to relational design.

It gets worse. The second design construct bypasses the explicit FOREIGN-KEY PRIMARY-KEY relationship mechanism.

 In order to code new business rules on the fly, without involving any DDL or going through the DBA (who is viewed as merely a babysitter), the designers resort to the following implementation of many to many relationships:

(Type1, Foreign_Key1, Type2, Foreign_Key2, Relationship_Type);

These five columns can relate any row in the database to any other row in the database, without altering metadata. Some, myself included, would say that they are really coding metadata as data. That's really convenient, but it can drive the person writing SQL against this mess nuts, unless it's very carefully documented.

It gets worse.

Sometimes, the fifth column above is left out, and the relationship type is inferred by the object that manipulates this data based on knowledge of the subject matter embedded in that object.

Also, instead of having all instances of the above quadruples in one table, they will be scattered in dozens of tables across the database. How do you know which tables implement which implicit relationships? "You just have to know".

In short, the database was not designed for data sharing between dissimilar subsystems that share a common model. It was designed for persistent storage of encapsulated data, and any data sharing is intended to take place by having objects talk to other objects.

That's fine, but the relational database usually outgrows that role, and the person stuck using a relational tool, like SQL or BO to manipulate the data is faced with a quagmire.

This has little to do with proficiency with SQL. It has a lot to do with data design.

--
Regards,
    David Cressey
    www.dcressey.com