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.
