Not all databases are created equal...

In today's IT environment, many think of the database as just the persistence layer of objects, or as a commodity. If your database isn't performing well enough, then just throw more hardware at it. While this belief is pervasive, it isn't true.

I recently had one example that shows that each database vendor had a different solution to the same problem.

I have a customer who had overloaded a table's purpose. The table stored different types of comment lines associated to an object. So the layout looked like:
id - the id of the object that it was describing.
type - the type of comment.
sequence - used if there are more than one entry of a given type for this comment
comment_text - the actual text we want to store in the database.

Now the problem arose due to a requirement that we only allow a single entry per id of a comment who has the type = 'c'.
All other types would allow multiple entries.

I looked for a solution in IDS, DB2 and Oracle.

In Oracle, because their indexes allowed nulls, you could create a functional index where the function was inline and returned either the unique id, or null if the id was already in the database.

In IDS, you had two solutions. First was a functional index that you'd either error out if there was an entry of type = 'c' for the given id, or you would return a combination of the id, type, and sequence.
The second solution... use their VII to create an index that mimics Oracle and you can use their solution.

In DB2, you'd have to create a trigger.

While I support all three of the databases at various clients, I have to admit that I loved the simplicity of Oracle's solution.

I also like the fact that IDS's solution as it would also work in Oracle, and that IDS has the ability to extend itself and mimic other features in other databases that are not native to itself.

I was less impressed with the DB2 solution, however I was told that it was extremely efficient.

The point is, while each of the two database vendors (Oracle, and IBM)[IBM owns DB2 and IDS], has the ability to solve the problem, they do so in slightly different methods. Each method has a slightly different cost and will effect performance.

An alternative would have been to separate the table in to multiple tables, the use of an Index had the least amount of development impact.

So the next time you look at choosing a database platform, don't buy in to the idea that they are all the same thing aka a commodity.