Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am primarily a web developer and I have a couple of personal projects which I want to kick off.

One thing that is bugging me is database design. I have gone through in school db normalization and stuff like that but it has been a couple of years ago and I have never had experience with relational database design except for school.

So how you do you approach database from a web app perspective? How do you begin and what do you look out for? What are flags for caution?

share|improve this question
Good database design for web apps is the same as good database design for any app. There are many introductory books available that do a good job of covering the basics. –  Robert Harvey Nov 10 '10 at 18:57
@harvey Any books that you might want to recommend? –  bron Nov 11 '10 at 17:17
add comment

closed as too broad by gnat, GlenH7, Kilian Foth, World Engineer Oct 25 '13 at 19:19

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

10 Answers

The best book I ever bought regarding database design was Database Design for Mere Mortals by Michael Hernandez ISBN: 0-201-69471-9. Amazon Listing I noticed he has a third edition.

Link to third edition

He walks you through the entire process of (from start to finish) of designing a database. I recommend you start with this book.

You have to learn to look at things in groups or chunks. Database design has simple building blocks just like programming does. If you gain a thorough understanding of these simple building blocks you can tackle any database design.

In programming you have:

  • If Constructs
  • If Else Constructs
  • Do While Loops
  • Do Until Loops
  • Case Constructs

With databases you have:

  • Data Tables
  • Lookup Tables
  • One to One relationships
  • One to Many Relationships
  • Many to Many relationships
  • Primary keys
  • Foreign keys

The simpler you make things the better. A database is nothing more than a place where you put data into cubbie holes. Start by identifying what these cubbie holes are and what kind of stuff you want in them.

You are never going to create the perfect database design the first time you try. This is a fact. Your design will go through several refinements during the process. Sometimes things won't seem apparent until you start entering data, and then you have an ah ha moment.

The web brings it's own sets of challenges. Bandwith issues. Statelessness. Erroneous data from processes that start but never get finished.

share|improve this answer
add comment

I do both object oriented programming and (mostly transactional, but some OLAP) database design, and for my circumstances, there are a lot of recurring themes (at least with OLTP).

Practicing 3nf normalization helps me to practice some variant of the single responsibility principle. A table should represent a concept in your system - and concepts should relate to one another such that they attempt to mimick reality; for instance, if I'm building a system where a Customer can have 0 or many Activities, then I create a Customer Table, and an Activity Table. The activity table has a foreign key relationship to the Customer table. When I'm building stored procedures, I would make sure to use an outer join to join Customer and activity because the business requirement that a Customer can have 0 activities.

I also watch out for opportunities for extensibility by using bridge (link) tables. For instance, if I were trying to represent a business rule where a book could have an unlimited (variable) number of authors, I would create a Book Table, an Author table, and a bridge/link table that has foreign key references to both Book and Author.

Also, I use surrogate keys on all tables (typically auto-incrementing identity columns, but perhaps Guids - the tradeoff with guids in code is that they take up more memory space than a simple integer), and I avoid relying on natural keys for my lookups (except with Bridge/Link Tables). By default, I also create indexes on common foreign key columns, and review stored procedures/system queries from time-to-time to optimize indexing strategies. Another indexing strategy I use is to look for places in my code where I build a collection based on a search column, and add appropriate indexes to search columns.

share|improve this answer
add comment

I design my database schema first, then use an ORM to create the objects from it. I'm a bit old school that way; I don't trust ORM's to create an intelligent, efficient database schema. That is the work of humans, and part of the craft of software design.

share|improve this answer
The ORM doesn't invent your schema. It builds it based on what you have done in your objects. If you build your objects from your schema, you are actually delegating an important task to your stupid ORM. –  user2567 Nov 10 '10 at 19:06
@Pierre303 The schema is built off of the programming rules inside that ORM which may not perfectly mesh with your situation / design. It may create an suboptimal databse. I have seen some horrible stuff come out of ORMs even at the query level. –  m4tt1mus Feb 17 '12 at 19:46
@Pierre303, I think this comment shows exactly why it is bad idea to build from the ORm, a properly designed database should not directly match the objects used in an application. There are often many other things needs to properly design a database that this would not consider nor deos this consider what structures are most efficient for the database not the application. –  HLGEM Feb 17 '12 at 19:55
@HLGEM: you can't have possibly worked with advanced ORMs like Hibernate and write that comment –  user2567 Feb 17 '12 at 20:43
OH, how does the orm handle auditing and fields needed by things other than your application? –  HLGEM Feb 17 '12 at 20:48
add comment

I found Bill Karwin's book, SQL Antipatterns, to be really useful for database planning. The point it makes most comprehensively is that the database offers many opportunities for protecting the integrity and meaningfulness of your data, and that it's a common mistake of designers to ignore these features for various tempting reasons. Considering these issues right from the start and letting them inform the entire design is worthwhile and beats trying to paper over cracks later.

I prefer to use a database that has comprehensive constraints to enforce business logic and integrity at the database level. Often I see the database as the application and anything that accesses it as a mere interface. This makes the addition of other "interfaces" a more pleasant and straightforward experience, and has positive benefits for security.

I also think it's important to consider the structure of the database as a changing entity, rather than assuming you need to wrap it up and seal it before starting anything else. You should plan for change and accommodate the DB in your versioning system. There is a nice essay on this: Evolutionary Database Design by Martin Fowler & Pramod Sadalage (and also a whole book on the subject by Sadalage, though I haven't read this).

Lastly, peripheral issues of user accounts/roles, hardware/location/connection of host, etc. are important and sometimes overlooked. Keep these in mind too when planning.

share|improve this answer
add comment

database design cannot be done completely without considering how the data will be used, so here's a short list of steps:

  • write short sentences capturing the relationship among entities
  • draw an entity-relationship diagram representing the sentences
  • create a normalized logical data model from the E-R diagram
  • make a CRUD matrix for applications and entities
  • use the matrix to verify coverage of the lifecycle of each entity
  • extract subschemas for each application
  • examine the navigation paths over the subschemas for each major/CRUD operation
  • consider the reports that will be required
  • design the physical data model based on all of the above; denormalize, partition, and use star schemas where appropriate
share|improve this answer
+1 for the first sentence at least. –  Emmad Kareem Feb 17 '12 at 20:02
You better make sure you get the report right if you plan on pleasing the people who write the checks. –  JeffO May 18 '12 at 16:14
add comment

To successfully design a database you need to consider several things first:

  • What data do I need to store and how is it related to the other data I store. How will this data need to change over time? Do I need to be able to see a snapshot in time (that order from 2009) or do I only need what is current (active users only)?
  • How can I make sure my data is meaningful and maintains meaning over time (data integrity)?
  • How can I make sure that data access is fast?
  • How can I keep my data secure?

So before you start to design a database you first need to learn about normalization and the features of a database used to keep the integrity of the data.

Then you need to understand performance tuning. This is not premature, performance is the critical failure point of most databases and it is very hard to fix once you have millions of records.

And finally you need to understand how to secure the data and what data needs to be secured and what internal controls you need in place to ensure the data is not maliciously changed or to ensure you can track the changes over time to find out who and when a change was made and to be able to revert to previous versions.

It is also helpful to read a bit about refactoring databases before you start as there will need to be refactoring later and it is helpful to know how to set things up so that you can refactor as easily as possible.

In general the data outlives the application by many years, it is the heart of the application and should not be considered as some dumb datastore that is mostly irrelevant.

share|improve this answer
add comment

In general terms, good database design is good database design - the bigger question for web use will be how you access the data and manage things that one might consider require state which basically the web doesn't have.

Thinking about it, my approach is based on really rather a lot of experience... but whether you start with schema or objects you're actually trying to do the same thing i.e. build a useable model of your data - for a substantial number of projects there is liable to be a fairly direct relationship between model and schema (not in all cases, and probably not for all tables/objects) so really its a matter of building a decent model starting wherever you're comfortable and working from there.

In terms of building a decent model - @Tim has it for down for databases and fundamentally building your object model is going to be broadly similar - what's unique, what's a hierarchy, where are there many to many relationships, etc. then, however you get to a database, make sure that you do all the good stuff.

Also make sure that you have scripts or ddl in code to allow you to create the schema from scratch and to update as you make changes (ddl in code is my preferred method - I have a system and it works).

share|improve this answer
add comment

I start with a big whiteboard and a bunch of different colors of pen. Different colors mean different things. And I just start drawing. Usually I draw things that are definite in black, things that are likely in blue, and things that are unlikely in green. Red is for important notes. I erase and redraw copiously. I think about what kinds of things I need to query and make sure the model supports it. If it doesn't I'll tweak until it does.

Eventually if the model gets too big I'll move it to Visio and work on pieces back on the whiteboard.

Last I think about extension points. The biggest mistake I see most people make is design their database and then say "I'm done with the database" and move on. You're never done with the database. Every single change request you get is likely to go all the way down to that level. So think about how to add on to it. Think about what kinds of requests are likely and see if you're able to hook them in. If you don't think at all about extensibility, you're going to go into major design debt when those change requests come up.

As for "SQL then ORM" or vice versa, that's up to you. Just make sure that your model makes a good foundation first.

share|improve this answer
Tricky one this... I agree that one needs to consider the future of the project (and the rest is good, hence upvote) but I've more than once had databases that have fields and even tables that end up never getting used because I designed in a future that never happened. I tend now strongly towards building to solve the problem at hand - but (and this is my "get out of jail free" card) I make sure I have a mechanism that allows me to easily update the schema (and since I do so from code can apply complex manipulations in the process if necessary) –  Murph Nov 11 '10 at 9:13
That's exactly what I was trying to get across. Build what you need, nothing more. But if you don't plan for expansion later, well, have you ever been in rush hour traffic in the bay area? That's a perfect example of what happens when you don't think ahead to how you might need to expand. –  Hounshell Nov 11 '10 at 21:56
And to clarify the colors better: Black is for things that I know are correct. Usually simple things that there's not really any other scheme that makes sense. Blue is for things that I may decide to restructure slightly. Things that are probably right, but I may erase. Green is for things where I'm really brainstorming and I'm pretty likely to erase. –  Hounshell Nov 11 '10 at 21:56
add comment

I design objects first then I use an ORM (such as nHibernate) to create the schema. It gives me a lot more flexibility than doing the inverse.

The next step is optimization of the generated schema.

It has been a long time since I have seen a project where the database tables where designed first.

share|improve this answer
Yes. Unless you are a DB guru keep the db as simple as absolutely possible. It should only be good enough to support the app. Pre-optimization is bad. Pre-optimization when you don't know what you are doing is terrible. If you run into problems (and you probably won't) only then bring in a real expert. –  ElGringoGrande Feb 17 '12 at 18:49
@ElGringoGrande Unless you are a dbguru, you have no business designing a database for any but the most rudimetary application. If it will need more than 10 tables and will hold no more than 100000 records and you don't have a professional database designer, then you are doing it wrong. –  HLGEM Feb 17 '12 at 20:09
Well crap. I designed a database that has over 160 tables and has millions of rows (the largest table has just over a million records for a medium size customer. The largest customer has over 5 million). Most customers have several hundred concurrent users and the largest over 2 thousand users. And I am no DB Guru nor did we hire one. I have done several of these DB designs for sever different applications. Boy did I screw up. –  ElGringoGrande Feb 17 '12 at 21:06
ElGringoGrande: If you have designed such databases, with hundreds of concurrent users and millions of rows in the tables and the users are happy, then you are db-guru. Perhaps you haven't realized it, yet. –  ypercube Feb 17 '12 at 21:37
add comment

Few things not explicitly stated so far by other fellows:

  • It is best to have the database design done by someone who's professional. It is OK to learn of course, but I would not suggest building a medium or large model if one is not well versed in modeling or database design. The reason for this is that the cost of a wrong design is usually very high.

  • Know the system objectives and user requirements well. Without knowing the requirements you can't design the correct data model.

  • Know which code to do in programs and which code to let the DB take care of. This is required so that you set the data column's null, not null, etc. properly. This is also required so that you specify your RI correctly.

  • Determine your primary keys well. Go for simple keys when you can.

  • Consider integration needs with other applications.

  • Consider using universal data models and follow industry standards in naming and data column size.

  • Think of future needs (when known and when applicable)

  • Have your mode reviewed by others.

  • Use a tool for modeling - Either an ERD tool or a UML tool.

  • Review and understand generated DDL code. Don't take it for granted.

share|improve this answer
add comment

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