3

My question consists of three parts:

When can I be sure that my database design is perfect?


Is returning to the database design to change some issues (i.e, adding new column, deleting a column, changing data type, add new table, etc.) considered a bad practice or is it normal?


Are there any websites or books just for training on ERD and normalization? I want a lot of samples, practices, and case studies with recommended answers, to strengthen my skill in database design and avoid the poor database designs I've made.

Note: I don't need books to explain the concepts, what I need is practices, examples, and case studies with recommended answers.

3
  • 7
    I suggest you split your question and repost the part about books as it seems independent of the other parts Aug 18, 2011 at 9:30
  • 3
    @just_name - "When to be sure that your database design is perfect?" this is an open ended question and it will make sense only if you have any specific scenario. If you agree, please remove it and rephrase the other two question to one... Aug 18, 2011 at 10:46
  • 1
    "I have three questions" ... so post three questions. Except ... "I want any websites on ... " that's a job for google. So you have two questions.
    – jcolebrand
    Aug 18, 2011 at 15:11

3 Answers 3

6

1) When to be sure that your database design is perfect?
Your design is never perfect because the business logic and amount of data is always changing. Perfect is difficult to define I've seen systems that were great on deployment but had poor performance after a few years of data were added. The regrettable trend to treat a database like a black box by some application developers means some databases are deployed with critical tables lacking primary keys or indexes. Perfect to the CIO because they got the application delivered on time and on budget could be a pain in the butt to the developer/ DBA who has to deal with the problems.

Here are some of indicators I would look for which indicate your design is ready to go
a) Extensive use of primary keys, unique keys, foreign keys, indexes, more so on the larger tables ( I only mention this because I've seen commercial products which lack this)
b) Application logic is duplicated as far as is practical in the database with the use of constraints and default values. You cannot capture everything but just knowing that there will always be a value for an entry provides peace of mind.
c) Test, test, test: test from the user perspective on data entry, test from the manager perspective who wants an overview, test from the analyst perspective who wants to see trends If you find yourself joining 9 large tables which require full table scans in order to find out what work is assigned to a user then maybe you need to reexamine things. Not everything can be simple but excessive complexity to answer user needs is a hint of trouble to come.
d) do some daydreaming on how the database could and could not be extended. If I need to add a new property to a unit of work how hard is it? Can it be done without table changes? If you are asked to add a new type of work how hard is it. (Work could be a product, a transaction, a case)
e) people and organizations provide hours of work for me. How easy is it to create, edit, de-duplicate and report on them?
f) how many user languages are you supporting? What character sets will be required. If, for example, you intend to support English and Spanish, what happens if the design must be extended to cover French and Italian?

2) Is returning to the data base design to change some issues (like adding new column, delete a column or change data type or add new table or ....) considered as a bad practice or is it normal?

I would say it is normal for an application where the business logic changes frequently or the end user requirements are being added to.

5
  1. It isn't. But because the database is usually foundational to the business (eg applications are built on top of it), it is worth working hard to get it in the best shape you can. "best" is a practical consideration and depends on your needs, not a measure of theoretical or ideological perfection.
  2. Requirements change - if your database doesn't change in step then it is going to end up a white elephant everything else is built around rather than the solid ground everything else is built on!
4
  1. Database design is perfect when database server performance is good under normal usage in production. Let me explain this: I administered lot of databases with perfect design. Entities, relations, and so on - all was perfect. But this systems wasn't tuned for real usage. If your users reports about low performance, you better denormalize your tables than answer to users 'my design is perfect and I cannot see how to make it faster!'.
  2. Returning to DB design is normal. I think if you found new issues in design, it is better to change the design than 'workarounding workarounds'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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