about database design

Question

My question consists of three parts:

When to be sure that your database design is perfect?

---

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 want any web sites 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.

Answer 1

1. Perfection is a state of mind. What appears perfect to some, will appear less than perfect to others. Design is often compromised by decisions that must be made without all the information. Subsequent revelations will make any previously 'perfect' design seem less than perfect. When thinking of perfection I am reminded of this famous quote:

   Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupery French writer (1900 - 1944)

2. Expect your database schema to change almost as much as your code.

3. The Data Model Resource Book series offers many example relational database schemas for various applications and industries. The design of each schema is explained in great detail. Perhaps reading from these books will give you the guidance you ask for.

Answer 2

To answer the first two:

• When to be sure that your database design is perfect?

It never will be. Business requirements are always changing, and so will your database.

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

See above. It's normal, so long as you are not doing it every few days. Adding new columns is a relatively frequent activity - changing the type much less so.

Answer 3

There is no such thing as a perfect DB design.

Your schema will evolve along with the needs of your application. It is perfeclty normal.

New features in an application might lead to introduce changes to an existing schema. As might performance and scalability considerations.

Wikipedia has good references if you start from data modeling.

Answer 4

You're better off having specific needs and then finding solutions instead of some examples of good design that met some other need. There may be good examples out there, but I'm not sure you'll get enough information on why every design decision was made/what problem it solved. Normalization is great, but in some situations causes more problems with performance than it is worth.

Review those problems you've recognized and look for specific solutions. You'll learn a lot more along the way.

Answer 5

1. Don't try too make your database design perfect, make it pragmatic instead.

2. Normally no, but it's not always easy to change a shipped database so design it in a way that will make schema upgrades the less painless possible.

3. There are plenty of resources available on the net. Take a look a this normalization tutorial with practical examples for a start.

But most of all, the database design may evolve quickly in the initial stage of developing your application so focus more on the storage needs of your application than on pure and theoretical database design.

Answer 6

When to be sure that your database design is perfect?

Your database design will never be perfect. IMHO, the goal is to develop a functionally working database that allows for scalability.

For example: Initial design built from a one-to-many perspective. (Each user has only one role) This will not scale well if requirements change so that each user can have more than one role.

Always keep scalability in mind when designing a database. Think about making you database design more like a power-strip than an extension cord with one plug.

Is returning to the data base design to change some issues...considered as a bad practice or is it normal?

No during development and beta testing. Requirements can be misunderstood or mirepresented and cause design changes. Reports may not give enough detail at first glance. This also leads to end user "Ah Ha!" moments. If Report-A gives us this information then Report-B and Report-C can give us this information. Perhaps the data does not exist in the design for Reports B & C.

Scope creep can also cause design changes. One way to prevent changes is to treat these new "Ah Ha" requirements that surface during development as Phase 2. (scalability once again)

Yes, if this is in production and there are several minor tweaks that were not part of scope changes but shortfalls or shortcomings.

Experience is the best teacher of these issues. Once you get burned buy a desing that is not scalable you will learn to think in terms that allow for easy integration of change.

Answer 7

Question 1

There is no such thing as perfect, but in a good design:

• The data will be in 3rd normal form, or you will at least know where it isn't and why it isn't
• You'll set up appropriate primary keys
• You'll add foreign keys and enforce them where appropriate
• You will consider which columns need to be indexed
• The columns will have the appropriate type for the data they contain
• You'll be following a naming convention for tables, columns etc.
• You will (as far as possible) keep business logic out of the database.
• You will consider concurrency issues in any sprocs
• You will apply appropriate security measures

A normalization issue that I come across quite frequently is developers who claim that a database has been normalized, but they've missed something. A common example is address data where there is a single table whose contents include street name. I don't have a problem with this design (it is often fine in practice), but I do have a problem with people not realising it isn't strictly 3rd normal. Truth be told, I see this type mistake quite a lot in my own code... but I'm learning to spot it more quickly.

Question 2

Returning to database design to change it in the light of new requirements or understanding is normal and highly desirable.

That said, bigger changes can be relatively costly where they impact on other things like indexes, sprocs, business model etc. so you should always do your best to avoid these changes where possible. The best way is to gather as much info. as possible before you design, then do as much design as possible before you code, and then do what you can to reduce the dependencies between different parts of your system.

Question 3

Peer reviews are often more valuable than exercises with model answers (which are sometimes wrong and are almost always closed for discussion). If you have a particular design you want critiqued, then there are plenty of forums on the internet where people will be more than happy to help.

Answer 8

As other mentioned, perfektion is something you could never define or reach. Its very subjective and it depends on the view. So I would say, a data model is good when it is valid and fit's the requirements. :-)

So in practice you will allways extend your database design during the project. The most important part in this project is to keep the design clean and stable. Databases are allways growing so there is no way to ensure from the beginning, that you have all the columns you need, nothing more nothing less.

Learning Database design isn't only a question of practice. Its a practice of understanding. Be sure, that you are able to understand the business. I have learned it quite well with taking some business examples and designing the business data needs. And it has worked for me very well. ERD, ERM etc pp. are only tools to visualize the structure, not the hole process.

The book Data Modeling Essentials is a realy great book. It provides alot of techniques and practice examples. Its maybe, for me, the best book for learning data modeling. Maybe you should take alook inside.