How to convince someone to normalize a database?

Question

So I’ve been working on this project at work where I’m coding a php website that interacts with a database I have no control over. The database was “designed” by a co-worker that has been with the company many more years then I have; so in the end decisions are left for them to decide.

When I was first pulled aboard on this project I went to co-worker and explained that the database schema seemed flawed. I explained the importance of normalizing the database to insure data integrity issues, disk space savings, and that it would make the programmer’s (me) job easier. I even gave examples of how insertion, deletion, and update anomalies could occur in the current design. Nevertheless the co-worker explained to me that they did not want to over complicate the project’s database, and that it would not change period.

So now I’m a couple months into the project and I’m pulling my hair out each time I have to join two tables to insert a value in an attribute that has a one to one relation with each other. (So the attribute should have just been an attribute of the main relation.) The database looks horrible, and I’m afraid that years down the road this will come back on me since I programmed the front end that uses the database.

Does anyone have any suggestions as to how to talk a “superior” co-worker into correctly designing a database? Or any suggestions on how to avoid getting patronized years down the road for a design I didn’t have any part of? Should I just refuse to work on projects like this in the future? Leave a comment in my code saying the database wasn’t my doing?

Thanks.

Edit: Additional information in response to comments...

I know that the de-normalization of a database can be useful for speed purposes, so I’m not overlooking this. For those reading who haven’t heard of this tactic I’ll illustrate an example. Often database designers have an address relation that lists a user’s street, city, state and zip code. While everyone knows that a zip code determines the city and state, therefore constituting a table indexing zip codes to city and states. Often database designers will combine the two tables, de-normalizing them with foresight that every query for a user’s address would require a join from the address table to the zip table. This ultimately speeds up the querying process, and is sound reasoning for de-normalization of portions of a database design.

To fill in some details here the database is designed for a Tour Request system, so the data within is related to visitor information, dates, etc. The schema that the current database uses is unpredictable from start to finish. From the simplest inconsistencies in variable naming patterns (example: num_of_visitors, arrivalMethod, etc) to having separate relations defined for a single state one-to-one attribute. Example: statusID represents the status of the tour request, it can only ever have one valid state selected from a group of possible states (Approved, denied, pending, canceled.) For some reason the database has a status table containing: tour_id(Primary key of tour relation), statusID. This allows for multiple states to be defined for each tour request. Which, by design a tour request should only be in one state at any given time. So it’s a flaw in the design not an oversight of mine.

Answer 1

Change job.

EDIT:

The short answer is not because I'm joking or not taking the question seriously. I've been in such a situation before. The bad database is not the problem. The problem is blind or ignorant management. I mean if they don't know or don't care that important technical decisions are made by incompetent people then things will be worse. It's like walking into a swamp.

Seriously consider looking for a new job. There are truly great workplaces for a developer. This one is not. You're wasting your time.

Answer 2

From my experience, these types of situations often end up being un-winnable battles, unfortunately. A few things you can do to distance yourself from the design might be:

• Implement a data-access layer in the code that abstracts away as much of the actual database design as possible. This way, you can structure your code in a better format, and effectively "distance" yourself from using and being blamed for the bad database design.
• Create views in the DB to access data in a more logical format
• Make small refactorings to tables/code when you get a chance, if you can get away with it

I wouldn't put derogatory comments in the code, because it will most likely come back to haunt you. In your data access layer, you could put in objective/non-offensive comments explaining why you are abstracting away a particular design, and how it could be designed differently.

If things are really bad, and nobody else will support you, it might be time to look for another job.

Answer 3

Maybe you can specify the operations you need to perform against this database and suggest she implement them as stored procedures in the database? ... Definitely would put the problem where it belongs... with the person that caused it.

Answer 4

The most positive way would be to work with co-worker and try to evolve and educate their way of thinking. Perhaps discussing the mistakes that you've made in the past will be an easy ice-breaker and show him/her the implications of a poorly designed system.

If you don't have too much past bad experiences to help you out then I would suggest that you record how long (time or money) specific tasks/defects are taking to complete. You can then produce statistics, all managers love a graph, which will hopefully show how over time the length of time required to add functionality or resolve defects has increased.

Hope this helps.

Answer 5

This question could be rephrased to include any design and implementation regardless of problem domain.

It is a massive cause of frustration when you get into a situation like this. I have fortunately not gotten into these more than a few times and I have been able to largely avoid the parts of the SW that were affected. Or build a middle-layer that allows you to use a more sane database layout.

If the senior designer and management doesn't care/understand there usually isn't very much to do. It's the same anytime any change is required to sw that has been around for a while. Getting the changes approved by people who designed the system in the first place is often impossible for various psychological reasons unless you are the superior and can "force" the issue. And even then it might in some cases not be feasible (you might end needing too many changes to your sw).

One possibility would be, though, if you have specific problems such as performance. If you can demonstrate that a better db design would solve these problems you could make some headway.

Answer 6

Trying to hide poorly designed database behind a layer is only a "hack" and IMO it should be plan B. For plan A, I would try to "escalate" to higher level.

As you described, you have no authority to influence the person messing the database. I would then go to the architect (assuming there is one) or to the project manager if there is no architect.

It is very important to sustain your arguments with well documented facts regarding the impact the bad design already has on the system you're building. Other facts could be well known issues for bad designed database from the specialized db communities.

I don't have enough information about your situation, but this is what I usually try to do when confronted with customers that insist on a poorly designed technical solution.

Answer 7

It is often the case that a developer needs to work with a client requesting absurd things or needs to maintain/work with legacy code that is very badly designed. Of course you should try to convince/educate your colleagues how a database should be designed, but your main effort should be to provide source code of best quality. More often than not, one needs to deal with such situations.

I would recommend to follow an advice already given to create a layer around the database. Fill it with comments like "Doing this complicated thing because db table 1 and 2 aren't normalized". Do not put criticism in your comments. Keep them strictly technical. Once in a while discuss with your colleagues/manager about database design. Buy a related book and put it somewhere for everyone to see. When someone asks, offer to lend it. Still, your main efforts should be writing good code.

Answer 8

Take them to the basement. Give them a choice between carrying a large couch or 4 small chairs :)

Answer 9

When she says she doesn't want to over complicate the database, maybe she meant she doesn't know or isn't competent in normalizing databases. In that case one way would be to try to convince her of the benefits of normalizing plus sending her to study database normalizing. One reason to normalize would be that just creating a database isn't the only requirement for a database. The database exists because it is used as data storage. And what stores the data? The application software. So the database creator should honor the software developer so much that she normalizes the database. Otherwise it would be a really awkward situation. To ease things out, you could show a few simpler normalization operations at first to get started with it.

Answer 10

You may not be able to persuade the database designer to rework the database, especially if there is already a lot of code that's written against the database as it now exists.

You do, however, need to expand your vocabulary for describing the difference between a well designed database and a poorly designed one. There are a lot of bad database designs that can't be fixed merely by normalizing. The one example you give is tearing your hair out because you have to join data from separate tables when a good design would have put the data in the same table.

Decomposing a table that should have been left composed is typically not a failure to normalize. Almost all failures to normalize result in composed tables that should have been decomposed. from your comments about coaching her on update anomalies, I'm pretty sure you already know whatever I might be able to teach you about normal forms.

Decomposing tables for bad reasons, sometimes known as "hypernormalizing", is a different flavor of design flaw. The programming problems that arise from this flaw are very different from the ones that arise from undernormalizing.

How many other programmers develop code that works on the same database? How do the rest of the programmers feel about the design? If they are real happy, that further reduces your chances of changing things. If they are all bent out of shape, you may be able persuade the designer by finding strength in numbers. I know, I know, that's politics, and I'll bet you hate politics.

Back when I used to teach programmers about database programming and design, students would often ask why there was so much politics in database work. I eventually came up with asimple answer:

When a database is in wide use, data sharing happens. That implies knowledge sharing. Knowledge is power. When power is being shared, politics happens.

Answer 11

Find a bug caused by the denormalisation. If the database doesn't have suitable constraints (and I'm guessing it won't in the circumstances), such a bug will exist. I'd put money on it. If you're using a bug tracker, look there. If not, solve it yourself. Either way, you can demonstrate how much damage such a bug can cause, and what they cost to clean up.

Answer 12

Show senior management this question. That will show them that normalization in some form or another is not just some 'complication' of a database, but standard procedure that the overwhelming majority of competent developers consider to be fundamental.