Nothing is better documented and understood in the field of information systems than the design of a relational databases. But sadly, too many databases have been designed or have evolved without following the correct approach.
It is challenging to design a normalized database, and therefore, it is easier not to bother. But most databases are designed once, and are used many, many times. If the design isn’t good, the designer is effectively abdicating responsibility for understanding and designing the data to everyone who ever queries the database. This is frustrating for those users and wastes money. Sheldon nicely elaborates these points in bullet 4 of his cited work in the endnote.
When confronted with an undesigned database, significant time is wasted writing queries that should be easier. Understanding and reconciling the data to the business is also challenging. And the underlying data structures drag the performance of the database into an unusable crawl. And by the way, precisely what is an ACCT_MSTR? Or a SHP_NO?
Here, we do not discuss how to design a database well, but how to cope with a database that has been poorly designed (or has not been designed at all!)
Coping with a poor database design
So you have a database with cryptic table and column names. Tables have a large number of columns that seem unrelated. You need data, but you have no idea where to start to look. And then there are colleagues, some of whom may be helpful and some not. After all, how to navigate your data is essential knowledge to being indispensable, or so the thought process goes.
Let’s look at the coping mechanisms.
Coping mechanism #1: SQL library
Every time you write a query, keep it in a query library, ideally stored in git. Every time you use someone else’s query, do the same. This will be your personal library. Anytime you need to write a new query, either amend one from the library and check it back in afterwards, or write a new one and add it.
Further, any time you find a document or diagram that describes the database, add those to the library too. And remember, the library is useless unless you use it!
Coping mechanism #2: User group
Start a database users community. Meet regularly. Have members share ideas and queries. Consider consolidating the personal libraries into one group library. Everyone benefits from sharing.
Coping mechanism #3: Build intermediate queries
Take a look at all of the queries you have ever written (they’re in your library, right?). Try to identify if they have commonality between them. It will be unusual if they don’t.
With that established, write a query that presents the common data as its results.
Finally, use common table expressions to write an inline temporary table that lasts only as long as the query is running. All queries that use the common columns now add the common table expression to the start of their queries.
Now you are querying a common set of data from what may be a very complex query. But the queries that use the output of the common query are much simpler.
This way, you are effectively redesigning a database that meets your needs (imagine the output of those common table expressions could be a table in the database).
Coping mechanism #4: Share the problem and suggested remedy
Invite the IT department’s database team to your user group. Share with them the work you have done. Ask them if there is a way you can create real tables for the common queries. Also ask them to review your SQL and suggest improvements. They like to do that!
Conclusion
Ultimately, there is only one solution to the problem of an accidental database. That is, to redesign it. But there will be excuses: “costs too much”, “insufficient ROI”, “too busy”. All of which may appear genuine.
But the evolutionary steps you have taken above will have helped to promote awareness of the problem. And that won’t hurt. And further, you will have already benefited in the near term from the steps you have taken.
What do you think? Do you recognize the accidental database? Can you think of additional coping mechanisms? Have you ever done any of the above? And finally, do you want to know more?
Let me know in the comments below.
Endnotes
Sheldon, Robert. “How to Get Database Design Horribly Wrong.” Simple Talk, March 6, 2015. https://www.red-gate.com/simple-talk/databases/sql-server/database-administration-sql-server/how-to-get-database-design-horribly-wrong/.