Software Engineering Stack Exchange is a question and answer site for professionals, academics, and students working within the systems development life cycle. It only takes a minute to sign up.
Sign up to join this community
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?
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.
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:
With databases you have:
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.
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.
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.
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.
database design cannot be done completely without considering how the data will be used, so here's a short list of steps:
To successfully design a database you need to consider several things first:
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.
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).
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.
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.
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.
