What is the easiest and most efficient way to design a database? From my perspective, there are a couple of options for an application's data store design:
- Design the database as best as you can initially before writing any application code. This gives you the advantage of having a base data structure to work off of. The disadvantage of this, in my opinion, is that you will have a lot of changes as application specifics that affect the what/where/how of data changes throughout the application development cycle.
- Design the database as the application comes to fruition. When you need some database objects as you write the application, you develop the database parallel (chronologically) to the application. The advantages would be less changes to the database structure as I see it. The disadvantage would be the division of time and development effort between application code and database development.
In your experience, what do you find to be the most productive and efficient method?
Best Answer
In addition to other answers...
Capturing your conceptual model first should define scope and requirements. From this, you can derive your logical and physical data models.
Once this is mostly static, then you have a stable database to build your application against. This is contrary to your first option.
Your second point will end in a messy, unmaintainable ball of mud. The data model will never be fixed: if you didn't design it up front, you won't have time to fix it before shipping. You'll be too busy hacking things together.
Minor changes to the schema, combining or splitting tables, changing relationships, etc. will happen, but in localised "islands," and your model + basic design will be unchanged.