Should you design the database before the application code is written?

Question

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:

1. 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.
2. 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?

Answer 1

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.

Answer 2

You'll be hard pressed to find any modern software department that isn't operating some variant of Agile. DBA's by comparison are stuck in the dark ages, with the kind of thinking that @RobPaller's answer contains still common place.

Modifying a database schema has never been as easy as modifying code, which is why there has been reluctance to embrace an agile approach to database development and maintenance. Now that we have the tools and techniques to operate in a similar manner to developers, we most definitely should. Just because it isn't easy to change schema, doesn't mean you can't and that you shouldn't.

I'm not advocating a haphazard approach to database design (see comments), merely an approach which more closely mirrors that of an agile development team. If you're part of an agile project, you aren't going to have requirements for work that may (or may not) occur in the future so design for what you know is needed, not what might be.

I guess that puts my vote with your option 2 and I suspect I might find myself standing in the cold on this one!

Answer 3

I've had the luxury of designing several medium-complexity databases, all used in businesses, with various front ends including web, Access, and C#.

Usually, I have sat down and worked out the database schema in advance. This always made the most sense to me. However, there has not been a single case where I did not end up making changes, adding new tables, or living with aspects that bothered me and were basically too late to fix.

I don't think the cure is to write the code first. And I don't think the problem is "insufficient business requirements" or at least, not one that could have been fully solved. The users don't know what they need and I don't have the power to make them think harder or be smarter or more aware or answer my questions better. Or they argue and I get ordered to do something a certain way.

The systems I build are usually in new areas that no one has gone into before. I don't have the buy-in from the organization, the resources, or the tools to do the kind of job a development team of top-flight design professionals could who got paid as a team ten times what I make to build things in twice the time.

I'm GOOD at what I do. But there's only one of me doing it in an environment that "doesn't do development."

All that said, I'm getting better at discovering business rules. And I know of a kind of third option (which I learned from an Agile Development Practices conference):

Before you design the database, and before writing any code, draw crude screens showing how the application will work. They must be hand drawn to prevent anyone from commenting on font or size or dimensions--you want function only.

With transparencies and paper pieces you can swap in and out, have one person be the computer, two be non-technical subject-matter-expert users (two so they talk out loud) and one person there as a facilitator who takes notes and draws out the users about their thought processes and confusions. The users "click" and drag and write in boxes, the "computer" updates the screen, and everyone gets to experience the design. You will learn things you could not have otherwise learned until far into the development process.

Perhaps I am contradicting myself--maybe it IS better requirements discovery. But the idea is to design the application first, without writing any code. I have started doing this in small scale, and it's working! Despite the problems in my environment, it's helping me get the database designed better from the start. I learn that a column must move into a new parent table because there are multiple types. I learn that the worklist has to have standing orders that don't come from the integrated order system. I learn all sorts of things!

In my opinion, this is a huge win.

Answer 4

Your logical data model should effectively capture the business requirements of your application. Your physical database design should be based on the logical data model combined with the necessary changes that you as a DBA feel are needed to maximize the efficiencies of your RDBMS.

If you are finding that you have to make numerous changes to the underlying database design through out the software development life cycle of your application it is indicative of two things:

1. Scope creep - You're allowing new requirements to be introduced at an inappropriate time.
2. Insufficient Business Requirements - Your data modeler(s) (or system analysts) did not sufficiently translate the requirements from the business analysts. This resulted in an incomplete or incorrect data model to support the requirements of your application.

That being said once an application has been turned over to production it is not uncommon to have to go back and make iterative changes to the data model to support the natural evolution of the application or underlying business processes.

Hope this helps.

Answer 5

For most purposes I'd choose Option 2: Build the database in parallel with the other components. Wherever possible take an iterative approach and deliver end-to-end functionality as you build each piece.

This does take a certain amount of project discipline. Apply normalization rigorously (Boyce-Codd / Fifth Normal Form) each time you change the database so that you maintain quality and don't end up with an ad-hoc and inconsistent model. Be as aggresive as possible with business rules and attendant database constraints. If in doubt it is better to enforce a constraint early - you can always take it out later. Be intelligent about the order you implement architectural components so as to minimise technical debt. Have a good set of database design guidelines that all the dev team buy into.

All this of course needs to go hand in hand with other good development engineering practices: continuous integration, test automation and crucially from the database perspective, creating test data. Test data creation of realistically sized data should be done in each iteration without fail.

Answer 6

In the world of architecture, the phrase "Form Follows Function" was coined and later adhered to when constructing tall buildings. The same should be applied to DB infrastructure and application development.

Imagine writing an app, deciding on-the-fly that you need a table here and a table there. When your app is done, you have a huge number of tables being treated as arrays. Looking at all the tables side-by-side, the tables will definitely appear to have no rhyme or reason.

Unfortunately, some developer shops will pick up something like memcached, load it with data in RAM (thus treating it like a data conduit), and have a database, like MySQL or PostgreSQL, behave simply as a data storage unit.

The incentive for using a database should be to look at it properly: as an RDBMS. Yes, a Relational Database Management System. When you use an RDBMS, your goal upfront should not only be to establish tables for storage, but also for retrieval. The relationships between tables should modeled after the data you want to see and how it is presented. This should be based on the cohesiveness and integrity of the data along with known business rules. Those business rules can be coded in your app (Perl,Python,Ruby,Java,etc) or in the database.

CONCLUSION

I would most definitely go with option 1. It takes proper planning, data modeling, and on-going data analysis. Yet, this should minimize database changes in the long run.

Answer 7

I have in mind the follwing rule: "you can only get from database the information which you have data to generate". So, I design first the database later the code.

Why? No matter what metodology/language/toolset I use, if all relevant data is well designed and stored in DB I can retrieve it. No matter if is in C#/Delphi/FORTRAN/COBOL/Assembly/VBA or Crystal Reports ; OO designed or event/data/whatever-driven; agile or waterfall. If the data is there, I can retrieve it if the tools I use can connect to database. I can create the sales reports if I can SELECT the orders for the quarter's revenues - even if I have to write it byte-by-byte on Assembly.

If the relevant data is not there or even if it's there but (un)structured in a way I can't retrieve the information I need - how to code it?

Answer 8

I think it should be done before there's any actual code for the application, but not before the application is designed.

My typical workflow, if working alone is:

1. Determine what the application needs to do
2. Look to see if any of the tasks can be broken down for reusable components
3. Determine how each task needs to interact with the data storage -- what sort of questions will they be asking of the data, how often will they write, etc.
4. Design the database so that it should be able to answer all of the questions we need to ask of it, and should perform well for the most frequent tasks.
5. Write the application.

As I frequently work as part of a team, and we're geographically dispersed (and across time zones), we tend to have an initial kickoff meeting:

1. Determine what the application needs to do.
2. Determine where good points are to break the application into self-contained components
3. Determine how each component will need to interact with the others.
4. Agree on an API for each of the interactions.

Then, we go back home, write our part, and if a component needs its own local storage, so long as the maintainer for that part keeps the API to their module consistent. The main data storage is handled as a module with its own API, and people are expected to write to it. (in cases where DB speed is critical, the API is the table definitions, and if changes are made, we use views or some other mechanism to present the older version until all of the modules can be updated)

Answer 9

As usually, it depends ;)

For instance, suppose that we have a small-size working prototype developed in Python and using flat files, and the users are happy with the features of the prototype, so all we need to do is to productionize it, using RDBMS as its back end. When this is the case, it is reasonable to expect to do it right the first time - the problem is small and well-defined. In such cases designing up front is feasible.

On the other hand, when we are discovering the requirements in an Agile environment, we need a few iterations to understand them better. In such situations the database evolves with the rest of the application. This is what we usually do. Because we can refactor live OLTP tables without any downtime and with low risk, we are comfortable with the possibility of database refactorings.