How to design a database?

1. Select all the entities (objects)
2. Define a set of fields for entities
3. After the entity is created, produced normalization — at least to the 3rd form of lead you want
4. Again looking into the requirements specification and throws about possible requests emitted heavy queries (the ones that pull a lot of data, or who Joinet many tables) and try to alter the DB structure so that simpler queries. Pit stop here: the most common basic queries are SELECTы, but there is that SELECT is relatively rare and much more important a quick INSERT of data, it is necessary to dance from here.
5. Again critically evaluate the structure, and again we throw approximate queries. Carefully look at INSERTы. Look what INSERTы can be put in triggers. Throws approximate the body of the trigger, rythem structure under it
6. Again critically evaluate the structure, see what we are missing. Part SELECTов trying to make a View and stored procedure
7. Again critically evaluate the resulting mess. Something is correct, remember that we missed something. Go back to point 3.
8. After we think that everything is done, describe each table and field words, describe in detail what is and what we did, where what is optimized and what for.
9. The next morning, looking again, its structure, horrified. Remember that yesterday made the description. Carefully read, understand that we missed something else. Correct complement.
10. Are the structure adjacent to the programmer and try to explain to him the flow of data. When it finally all comes to, asking what he could do. He gives advice, after which you will invent that "it was obvious". Again, everything remodel.
11. When the neighbor-programmer you tired and sent the fuck agreed with your structure, open a DBA, throw a schema placing keys. Again, we understand that missed something. Again remodel the structure.
12. Ithacany and evil go to bed. Tomorrow is another day :)
\r
© me

Material on this topic a lot even within the Runet. But first it is advisable to read about normalization and functional dependencies. Examples of good design, you can peep into opensorce projects, for example, on bugzilla.

Us , CCAP taught:

\r
• Subject area. Described (better in my mind) a simple Russian language — that we simulated. The database is always a model of something in reality. Coarsening of the main objects and the relationships between them, with no details. Tool — MS Word, notepad, Manageractions.
\r
• Create a conceptual model. It is already possible to draw some drawings in word.
\r
• Next comes the logical data model — it already has the embryo of future tables, field names and relationships between them. There is already a specified relationship types — 1:1, 1:n, m:n. It is already possible to draw in a simple graphic editor or on the Board.
\r
• Then the penultimate stage — a physical data modelwhen you specify the field types. At this stage the least painful to use UML class diagrams, for example, using StarUML.
\r
• And last but circular stage is data normalization. Vietnam what data is redundant and again, and take them into a separate table. At this stage it is already possible to connect to PHPMyAdmin or similar tool and create a table directly on the server.
\r
• If overdone — system may run slowly, and some places to do the denormalizing. There is help and load testing experience.
\r
• ???????
\r
• PROFIT
\r

If the subject field (your goal) is easy, some steps can be done in the mind.
This approach was applied not just in person was helpful.

I think you should start with the selection of entities and normalization. Based on the requests (a good projectplans) should be set indexes. If that's not enough to think about denormalizing.

The picture is incomplete.
Where the customer, who will certainly want to implement their clever ideas to the table structure/DB fields?

Here's more on the topic of eax.me/database-design

Thank you to all )