Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
+1 This question provoked a very interesting discussion. –  Fabricio Araujo Oct 20 '11 at 17:30

9 Answers 9

up vote 32 down vote accepted

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.

share|improve this answer
5  
And stability is important, because table and view names, column names, stored procedure names, etc., are the database's public interface. (And sooner or later there will be many applications sharing that interface.) –  Mike Sherrill 'Cat Recall' Oct 12 '11 at 10:35
    
I would say this is pretty idealized approach, my experience is that drastical change happens from time to time what we need to be is to be agile and quickly adapt to new requirements and keep refactoring. –  zinking Aug 21 '12 at 5:44
    
@zinking: I'm doing it the Agile thing right now. –  gbn Aug 21 '12 at 6:26

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.

share|improve this answer
7  
Adding lots of new requirements during the course of a project isn't "inappropriate". It's something that your development methods ought to support and encourage www.agilemanifesto.org/principles.html –  sqlvogel Oct 11 '11 at 21:05
1  
I'm well aware of some of the principles of agile development and have been a proponent of them in a data warehousing capacity where it makes sense for the business. Thanks for your comment. –  RobPaller Oct 12 '11 at 4:42

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 see a kind of third option:

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.

share|improve this answer
1  
+1 Excellent answer and very informative! –  Thomas Stringer Mar 16 '12 at 11:45

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.

share|improve this answer

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)

share|improve this answer
1  
The case for option 2 is that with an agile methodology you don't know 1, 2, or 3 beyond that which is scoped for the next sprint. Change is inevitable, in scope, requirements and expectation. –  Mark Storey-Smith Oct 13 '11 at 20:45

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?

share|improve this answer

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.

share|improve this answer
1  
Do you not think some upfront thinking would be needed to define conceptual model? –  gbn Oct 12 '11 at 0:09
    
Some upfront thinking may be useful but attempting to define the whole model in advance is usually counterproductive. The model should align with business requirements and fit with project deliverables (app included) as they evolve. You cannot and should not expect those things not to change. Also creating the whole model upfront can actually obstruct other development because of the need to create dummy interfaces to support as-yet unused parts of the schema. –  sqlvogel Oct 12 '11 at 0:26
    
I suspect @dportas and I work in similar environments :) –  Mark Storey-Smith Oct 12 '11 at 0:38

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!

share|improve this answer
3  
Agile and databases do go together with caveats. Agile is borderline for VLDBs: there may not be enough time to validate and test changes to billion row tables between deliverables. And "agile development" isn't the same as "wholesale changes" because of a lack of forethought –  gbn Oct 11 '11 at 19:14
2  
Couldn't agree more re: lack of forethought but I don't think that's relevant to the question. This is not about whether you should approach design haphazarly but whether or not your data model should evolve as the application does. VLDB issues warrant an edit which I'll add. –  Mark Storey-Smith Oct 11 '11 at 19:33
3  
I read the question as "a new app with no DB yet" rather then "an existing app requiring changes to the DB" –  gbn Oct 11 '11 at 19:36
3  
+1 for the general sentiment of your answer, but "Modifying a database schema has never been as easy as modifying code" really depends on how much code you have (and how old it is). IMO the opposite is more generally true –  Jack Douglas Oct 12 '11 at 6:41
1  
+1 I am with you. –  A-K Feb 23 '12 at 3:51

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.

share|improve this answer
1  
@RolandoMySQLDBA, you are assuming that a database design built during app development will be a poorer design than one built before? Why? The opposite is often true. –  sqlvogel Oct 11 '11 at 21:48
1  
@dportas : My emphasis is on option 1 in terms of minimizing changes in DB design. I spent 2/3 of my tech career programming in shops where very complex data models and infrastructure were being morphed almost monthly on a whim. I cringed on such changes because business needs and goals were not etched in stone. I am pretty old school in this. Nothing wrong with being a little maverick as long as the design does not produce a lot of 'technical debt' (Yes, I read you answer) down the road. –  RolandoMySQLDBA Oct 12 '11 at 1:12
2  
+1 for 'use a RDBMS as a relational database not a bit-bucket for arrays' - whichever approach you take –  Jack Douglas Oct 12 '11 at 6:44
2  
@dportas: while this is true (business rules change), a well designed db will not change radically between an iteration (or sprint, or whatever) and another, since it reflects all the relevant data structures of the work process. If this happens (radical change), means a fail on the business rules capturing activities. –  Fabricio Araujo Oct 17 '11 at 17:17
3  
@dportas: not all DB changes, only RADICAL ones. Minor changes are part of the business of doing software. But having to split the data in 2 different databases in the middle of work are a failure on the design and the capturing business rules. (It actually happened to me. –  Fabricio Araujo Oct 18 '11 at 18:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.