The right way in designing a database

Question

I started my first MySQL project designing the ERD, logical and physical diagrams.

A friend of mine is making the same project as me. I started the plan of my databases by making an ERD and then normalizing.

However, he uses a relational database diagrams where he designs interfaces and other parts first before making the ERD. He for example writes "stack" only to the column phonenumbers, instead of making a "help-table".He says that it is best to make the interfaces first and then make the ERD.

Which one of us is doing the plan in your opinion better?

Answer 1

I'm not sure what you mean by "interfaces and operations", but the way you're designing the schemas is right -- doing an ERD and properly normalizing. A lot of people, when they are just starting out, will take shortcuts on the design in order to fit the schema to their current level of querying skills.

For example, instead of creating a table of phone numbers and mapping these phone numbers to a "customers" table, they might just stick in columns called Phone1 Phone2 Phone3... instead. That can be the kiss of death later on when designing your queries.

So my advice... Create the normalized data model with ERDs. Then read up on VIEWs and user-defined functions in order to "flatten" out your schema where necessary for people who wish to query it. Sorry for the general answer, but it's kind of a general question...

Answer 2

One could, and many people have, written a book on this.

However to generalise what I would generally do is

1. Analyse your data and reduce it down to 3rd normal form. This should be pretty formulaic to accomplish.
2. In light of likely business use of the data decide if and where data should be denormalized. Typically most databases will be overwhelmingly in 3rd normal with a few critical exceptions. This part is where experience and craft come in.
3. In light of the above create any additional indexes that may be necessary, or modify existing primary indexes (which should have been assigned in phase 1).
4. Create views for user access as necessary. The number you require may vary from none (as in simple embedded application) to many (as in no direct data access to tables allowed).
5. Create any procedures you need, and possibly triggers (generally best avoided but appropriate for audit purposes).

In practice of course the process is considerably more iterative, but the general design path from data to interface holds true. Also it's a good idea when designing a database to keep in mind that you will want to change it later and try if possible to make that a reasonably straightforward task.