Maurice shares another relational database anti-pattern.
I saw a case where a table had rows with a single meaning from business rules. They extend the application and add a type to the row for new business cases. The table became very complex to query, since there was alternate rows for the original business case.
In the beginning getting the most current widget of given person was just a matter of getting the row with the most recent date. Then they add more rows on the same subject and a new column type to express different situations about the same subject for a given person. Instead of re-engineering the database they took the easy way.
The query had to express this: Try to get the row for case 1 and type 2, if there is no row of this type fall over row of case 1 and type 3 and so on… I had to write a fairly complex function to do this, with parameters to express all possible combinations of use cases. Needless to say that this table is more complex to update for the same reasons.
It would have been much more simple to put information into different tables! Developer have the reflex of thinking that adding more table makes the database more complex to query. That’s often the reverse. As you said, relational databases are optimized for joins.
To handle this type of one size fits all table, there is a set of analytical functions like row_number, rank, dense_rank, ntiles, aggregates with over clause and some binding conditions with other rows that come with the over clause, but people are not used to them. They are no so complex to use, but are not part of the average developer database culture.
Needless to say that a well-designed database needs a reduce set of operators to handle data it contains.
Are you using anti-patterns in your database design? Perhaps database design is not your forte. A good indication that your database design is not using optimized patterns is when you have to use the OR operation to accurately return your information. There are a lot of cases where OR is the best solution. But if it can be eliminated your probably have resolved an anti-pattern.
As I described previously, having NULL in a bit data type can be an anti-pattern. If you allow null you have to have an or in your where clause such as the following.
WHERE myColumn IS NULL
OR myColumn = 1
A good database design would either not allow null, or provide a default constraint for the value. Always having a value allows the column to be indexed, and the results found through a method different than a complete table scan.
Another condition where your database may not be optimized is when you must perform a COALESCE or ISNULL operation because your data may be found in two or more places. While we sometimes design our database that way on purpose, when it occurs incidentally, that can be a relational Anti-pattern.
Here’s another example I have experienced. You want to find an individual by any phone number associated with them. A common anti-pattern is to have the phone numbers in the individual table, such as phone1, phone2, phone3, and the set is limited.
The better pattern is to have a parent-child relationship. Create an indivudalPhoneNumber table that has the individual ID and the phone number, maybe even a phone type. At least this design allows for N phone numbers associated to that individual. This table may be indexed on the phone number, and the individual easily found.
Please note that I am not trying to get a distinct, only one record for any phone number. In fact, multiple individuals may have separate records with the same number. What we are resolving here is the need to use the OR operator to look for a phone number in Phone1, Phone2, and Phone3 to make sure we find the individual.
When your queries begin to get complicated, it may be an indication that your database design is too complicated.
Are there other symptoms of Anti-patterns you see? Share them here or by email to btaylor@sswug.org.
Cheers,
Ben