current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free.
up vote 1 down vote favorite

I recently joined a new position as SW developer and during the interview process everyone stressed the importance and the pride of the quality standards of the firm in each and every process they have. (This was one of the aspects that made me decide to accept the offer)

When I started inspecting the database I realised that it was far from being perfect, far from being good and far from being not bad. Also I realised that all my team mates do think that the database is complex because the business is complex.

What is wrong with this database then?

  • inconsistent naming
  • de normalised table (with conflicting data)
  • plenty of stored procedures and tvf with if/switch/case based on values in the tables themselves
  • tvf that depend on tvf that depend on tvf making the code unreadeable and unmaintainable
  • no FK constraints and in some cases PKs
  • no distinction between data abstraction and business logic (too many encapsulated TVFs)

I decided to bring the matter up during my next review because I believe my manager (non technical) does not have a clue of what is going on.

I need help to come with a list of aspects to consider to asses the quality of a database design and then assess the score of our database against this list. This will help at least my manager to take steps in the right direction (I hope).

Which points should I consider to asses whether a database is well designed?

Well designed means:

  • does it job
  • it does not break (or do so in a sensible manner)
  • it follows a structure that make it easy to maintain and expand
share|improve this question

closed as not constructive by ChrisF♦ Dec 13 '11 at 12:46

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.
2  
My advice: be adamant about your opinions and bring up one issue at a time. Don't unload everything in your employee review. –  Richard Dec 6 '11 at 17:51
    
Is there actually a DBA or data modeller in the team or (at least) the company? –  Matthew Flynn Dec 6 '11 at 18:08
    
I'm assuming that the de-normalized tables haven't been deliberately de-normalized for performance; #shudder#. It may also help if you can find out about some (preferrably expensive) data-fixing they had to do to deal with the PK/FK problem. And table-value-field stuff usually isn't the best way to deal with a problem (especially if nested). Maybe bring up a general 'there are design problems' in the review, and ask to schedule a meeting later, with the manager, and some of the other DBAs? –  Clockwork-Muse Dec 6 '11 at 18:08
    
@X-Zero No, the de-normalization is a side effect of a bad design, not an attempt to increase performance. And unfortunately there are no DBAs –  mhttk Dec 6 '11 at 18:13
2  
I'd suggest scheduling a different meeting up front, and not "highjacking" your employee review. This sounds like a significant topic, so time constraints will dictate either you won't get a chance to fully discuss it or your review will suffer. –  TMN Dec 6 '11 at 19:22

5 Answers 5

up vote 3 down vote

Is there any improvement you could implement (and thoroughly test) that would show immediate value to the company or the customer, while also correcting at least one of the quality problems you are seeing? If so, propose that change--or better yet, volunteer to demonstrate the effectiveness of the change in a test environment.

Also, if you have the background and the skills to serve as either a data modeler or a DBA, and you have interests in that vein, you might talk to your manager about the necessity of the role and your interest in performing it.

share|improve this answer
up vote 3 down vote

There are valid reasons for putting business logic in a database, so you better wait until you have more information. There may be some reporting tools being used that cannot utilize complex logic or it needs to be shared by apps outside their control.

What problems are being caused by the lack of normalization and foreign key utilization?

All of this eventually gets weighed against having to refactor this database. Have you ever had to refactor a large and complex database? Maybe those names aren't so bad afterall.

share|improve this answer
1  
I think refactoring of the DB is out of the question, you are right. I tried to get the history of some of the decisions and seems that a "let's patch it" has been one of the main principles. On the names is a long debate too. Thanks, this is useful. –  mhttk Dec 6 '11 at 18:50
    
on the FK side, yes, there are problems: you cannot enforce them without getting exceptions. (of course there are some business rules that justify it) –  mhttk Dec 6 '11 at 19:27
    
On the normalization side, no problems, usual stuff: redundancies and values in the same column that look just similar. –  mhttk Dec 6 '11 at 19:28
up vote 5 down vote

Let's start with hte lack of FKs/PKs. Do some queries to see if you can find orphaned data or duplicated data. Once you can show how bad the integrity of the data is, you are in a better position to propose a clean up plan and the insitution of FKS. If you can't find any orpahned records (given what you said, I'd find this highly unlikely), this may not be the best place to start.

Next start with the worst performing queries which I would be surprised to find are based on things like the TVF that call other TVFs or views that call other views.

Now take one and design the data the way you would like to see it stored and show how much easier it is to query and how much faster the query is. Once you have a track record for improvements that work, people will be more likely to listen to you.

Start withe worst things from a performance, security and data integrity standpoint.

A good book to read is: http://www.amazon.com/Refactoring-Databases-Evolutionary-Database-Design/dp/0321293533/ref=sr_1_1?ie=UTF8&s=books&qid=1268158669&sr=8-1

It will help you design a system to improve the mess one bit a time.

share|improve this answer
    
+1 a much more clear and specific answer than mine :). –  Matthew Flynn Dec 7 '11 at 13:03
up vote 2 down vote

I think it comes down to what you mean when you say quality. For instance, when an engineer says Quality, they mean "does what it's supposed to do, lasts a long time, and fails in a responsible manner."

Software developers seem to think more along the lines of "is easy to understand and change". Engineers tend towards the "big design up-front" and don't have the mind-set of making it easy to change later.

Your co-workers are talking about quality in the eyes of the end-user. They take pride in the fact that the users feel the system is rock-solid, well supported, easy to use, etc. If the user never looks at the database tables, then table naming doesn't really factor into that measure of quality.

share|improve this answer
    
good point. I added an edit to my question. I have not considered the end user perspective this way. However the system is far from rock solid or easy to use –  mhttk Dec 6 '11 at 18:24
    
@mhttk - I missed the fact that there are no FKs... that's just not right. –  Scott Whitlock Dec 6 '11 at 19:00
up vote 2 down vote

if the database meets their business needs and you have developers that can modify it as needed to meet changing needs then its already as good as I would push for as the new guy. If you can identify a few areas that could use improvement (not an exhaustive list) and share them with coworkers and try to get them to bring it up with managers, or at least let them know you intend to mention these things.

database changes are huge and probably the best place to have a "if it aint broke don't fix it" mentality.

share|improve this answer
1  
This attitude is why there are so many badly designed databases out there. This is broken and needs to be fixed. Any relationsal database without FKS and PKS is by definition broken. –  HLGEM Dec 6 '11 at 22:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.