How to design a database so the developer that inherits it won’t run away to another job.

This is exactly the the feeling I got when I took a peek at the database I’m working with.
I thought, oh gosh I made a mistake when I chose this work…then i thought, well, look at the positive side,
there are lots of room for improvement here. To be honest I cant wait to make some database refactoring, if they allow my hands into it.

Whenever you mention to do database refactoring, you cant help by noticing chicken skin on some project leaders.
It is like mentioning a curse, we are all so much afraid of Murphy’s Law and the main engineering principle:
“If it’s working, don’t touch it!” that we endure whatever we have for the sake of safety.

As usual I will keep this on my blog as a reminder for future use.
It’s taken from Scott Ambler’s site

1. Multi-purpose column. If a column is being used for several purposes it is very likely that extra code exists to ensure that the source data is being used the “right way”, often by checking the values of one or more other columns. An example is a column used to store either someone’s birth date if they’re a customer or their start date if they’re an employee. Worse yet, you are very likely constrained in the functionality that you can now support, for example, how would you store the birth date of an employee?
5. Tables with many rows. Large tables are indicative of performance problems, for example it’s very time consuming to search a table with millions of rows. You may want to split the table vertically by moving some columns into another table, or split it horizontally by moving some rows into another table. Both strategies reduces the size of the table, potentially improving performance.
6. “Smart” columns. A “smart column” is one in which different positions within the data represent different concepts. For example, if the first four digits of the client ID indicate the client’s home branch, then client ID is a smart column because you can parse it to discover more granular information (e.g. home branch ID). Another example includes a text column used to store XML data structures; clearly you can parse the XML data structure for smaller data fields. Smart columns often need to be reorganized into their constituent data fields at some point so that the database can easily deal with them as separate elements.
7. Fear of change. If you’re afraid to change your database schema because you’re afraid to break something, for example the fifty applications which access it, then that’s the surest sign that you need to refactor your schema. Fear of change is a very good indication that you have a serious technical risk on your hands, one that will only get worse over time. My advice is to embrace change.

Leave a Reply

