The Basics of Good Database Design in Web Development

The Basics of Good Database Design in Web Development

Databases are everywhere, including everywhere in the world of web development. Everything from the simplest blogs and directories and to robust user-oriented websites use databases. No matter how complex or simple the website and corresponding database may be though, each takes careful planning in order to run efficiently and also securely.

In this article, we’ll go over the basics of how to design a good plan for a database, no matter what its final intended use. For all database designs, there are a set of standard rules and best practices to follow, all of which can help a database stay organized and help to team up with the respective site in a smart and efficient way.

The Basics of Good Database Design in Web Development

What Functionality is Needed from the Database?

The first method for planning for a database is to simply brainstorm, on paper or otherwise, concerning what the database will need to store, and what the site will need out of it. Try not to think of the individual fields or tables that will be needed at this point – all that specific planning can take place later. The goal is to start with a general and complete view, and narrow down. It can often times be more difficult to add in items later, rather than get it right the first time.

Functionality
Image credit: binaryape

Think outside the database. Try to think about what the website will need to do. For example, if a membership website is needed, the first instinct may be to begin thinking of all the data each user will need to store. Forget it, that’s for later. Rather, write down that users and their information will need to be stored in the database, and what else? What will those members need to do on the site? Will they make posts, upload files or photos, or send messages? Then the database will need a place for files/photos, posts, and messages.

What information will they need to derive from the site? Will they need to search for their favorite recipe, be able to access member-only content, or need to look up products and their recently purchased or viewed products? Then the database will need a place to hold those recipes, a place for content that is defined as members-only or not, or hold all products and create a method to link certain products to a specific member.

Determining Tables and Fields

The next phase would be to begin determining exactly what tables and fields one would need in the database. This is the core of database design, and the most difficult part. Using correct methods for linking tables together, sorting the data within each table correctly, and grouping it or keeping it separate are all arising problems when it comes to database design. At this point, list out what tables and fields are clear at this point, trying to be as specific as possible. Through the process, items can be rearranged or reorganized to improve the database’s efficiency and security.

Use a Data Modeling Tool

Now that you know what the site will need to do, it’s time to organize what exact information needs to be stored. A great database design tool can be helpful for this; specifically one that can help set up visual database models, such as MySQL Workbench (for MySQL databases only) or DBDesigner4. Gliffy is also a great free online application for creating flowcharts and database models.

Model

There are also far fancier, yet premium, tools as well, my own favorite being Microsoft Visio (Windows only, $249.99). Don’t worry though, there are plenty of cheaper options as well, and of course, plenty that are also open-source, including the two mentioned above.

Become familiar with the common icons and standard visual elements necessary to create database models, and begin planning via flowcharts and diagrams ahead of time. This can sort out logical errors before any actual databases are created.

Relational Databases

Almost all databases are relational databases. This means that the tables in the database are related to each other in some way. For example, if a there is a member on an ecommerce website, that member may be related to certain products based on what they ordered last, or what they have expressed they are interested in. For a blog database, authors would have to be somehow related to the posts they wrote, and logged in users could be related to any comments they’ve left.

By using the techniques for relational databases, we can store plenty of information in an organized fashion within separate tables: one table for members, one for posts, another for comments, and yet another for products. Then, we can link the data between different tables together via unique keys.

Primary Key

Every entry in every table needs a unique primary key. This is the “social security number” or “bar code” for each entry. It is unique to each entry, and no other entry can have the same ID in the same table. Having unique usernames or product names in a database table is not enough. It is far more efficient, and best practice as well, to use unique primary keys. Even with other types of unique fields, a database is still vulnerable to duplicate records, which can later break code within the website.

To relate two tables we use a foreign key, which is just a number ID that references a unique key in another table, usually our primary key. As an example, below we can see that our first table for authors has three authors with their own unique ID. In the separate articles table, we link each article to an author via that ID. We can now look up the author for the first article, and vice versa, see that Tom has two articles, Mary has one, and Jane has none yet.

DB Table

This is a simple one-to-one relationship model. There are also models for one-to-many and many-to-many relationships.

Grouping or Separating Data info Fields

Within fields, it’s also important to know when to group certain pieces of data together, and when to keep them separate. A good way to determine which information should be in the same field or otherwise is to think about what it would take to change that piece of information if necessary. For example, would it be necessary to place a full address in separate fields, based on 1) street address, 2) city, 3) state, 4) zip code, and then 5) country?

Is it essential for the functionality of the site (perhaps users or admins would need to search addresses by state only), or is it just a waste of fields and database space? If it’s not essential, just to change an address the database would have to update five separate fields, when it could just update one field in string form. In order to keep such a field organized, one could take in the information via an HTML form with these fields separated, but then concatenate them into one single string before placing the address into the database.

This is just one example, but always keep in mind the most efficient ways to organize table fields, and when to combine them, or when to keep them separate for the sake of the website’s functionality.

Database Normalization

Database normalization is a set of guidelines created by the community for organizing data in a database efficiently. We’ve mentioned a few of the most important and basic practices already, which are included in some of the most standard normalization forms. There are five normal forms to follow, and it’s a good idea to learn about these five forms in order to conform any database’s design to their best practices.

Database normalization is a large topic, but just understanding the basics can help tremendously. To take a look at each normalization form and a general overview of the concept, be sure to take a look at Database Normalizaiton Basics.

Conclusion

Database design can be a heavy subject with a lot to cover, but it doesn’t take a lot to learn the basics and get a good design for the most basic of database structures. Perhaps the most important rule and phase to designing a database is the initial design and brainstorming phase. This is what allows any developer to get all of the information they need up front, and to begin organizing as necessary. Only with all of the necessary information to work with can a great database design be created intelligently, with tables linked properly, and best practices intact.

The goal of any database is to be efficient and scalable. Data is always being edited, added, and deleted, so it’s important to keep a database organized in order to maintain this constant changing set of data. Be sure to design a database that deletes only the information necessary when needed, adds no duplicate records, and is able to reference other data throughout the database easily and simply.

Further Resources

Kayla Knight is a web designer and frontend web developer. She specializes in responsive web design, progressive web technologies, and also knows her way around most CMS's and PHP. You can find out more and check out her portfolio at kaylaknight.co.

Comments

    • Jesse,
    • March 18, 2011
    / Reply

    I know this post was more about the actual designing of the DB, but I think more information about the processes one should take before getting to the DB Design would be helpful. Persistence is an implementation detail and shouldn’t take a backseat to the actual processes/domain your application is modeling. More care should be taken in the defining of the domain model, the DB can come later. If you are starting from scratch, and you begin modeling your DB, you are potentially letting an implementation detail corrupt your domain.

    Figure out what you’re application needs to do, fully and completely and then worry about how to persist it. Maybe you don’t use a relational DB, maybe you use a document DB or object DB or flat files. Whatever it is, it shouldn’t rely on how it stores it’s information…ideally. I know this was touched on briefly, but it’s more important than I think this article lets on.

    Also, it would have been nice to see some information on the, so-called, NoSQL movement. Stuff doesn’t have to be stored in a relational DB. Look at what Facebook and Amazon have done. Look at CouchDB, or MongoDB or RavenDB. You want to talk speed and scalability…

  1. / Reply

    This really takes me back to 2002 and my ‘Intermediate DB Management’ class. We had this delightfully monstrous textbook that was nothing but theory. Our professor was at least kind enough to make us practice in the lab with dual assignments in both Access and MySQL (linux command line only). I really miss that class. The group assignments were especially fun. The video rental DB we made for our mid-term project was my fav. It was certainly better than the dry, boring Oracle class I took the following semester.

    p.s.
    Regarding the last example in ‘Relational Databases,’ it appears to be one-to-many. One author can be related to many articles, but each article is only related to one author. It’s 2am here. I could just be going crazy.

      • Shawn Mealey,
      • December 18, 2012
      / Reply

      I noticed the one to many relationship there as well and I was was a bit confused for a second.

        • Brian,
        • December 18, 2013
        / Reply

        Me also. This really makes me lose confidence in an article like this. How can I trust it when there is such a blatant mistake made so confidently. It makes me wonder what else might be incorrect that’s not so easy to spot…

  2. / Reply

    Good Database design helps you to make work easier and a beginner will easily start with development part. Thanks for sharing this beautiful and informative article

    • Jimmy,
    • April 11, 2011
    / Reply

    Almost all databases are relational databases. This means that the tables in the database are related to each other in some way.

    ^^You were way off here. This is a common misconception though. “Relational” is a term taken from the mathematical theory used to develop the model called first-order predicate logic. It does not mean the the tables are related to each other. Other database models like the network model and hierarchical model also have tables that are related to each other.

  3. / Reply

    Great article and thanks for sharing.

    Just to add that along with the base normalization rules it is good to know a bit about normalization too. While the purpose of normalization is to disallow you to duplicate data sometimes this is necessary. The reason is that joining multiple table in a heavy SQL queries may provide a big “table lock” and ruin your DB performance.

  4. / Reply

    Hi Kayla,
    Liked your article a lot. Liked the way you explained everything about database design, primary keys, foreign keys, database Normalization and their importance. Thanks for sharing.

  5. awesome!
    essential, gorgeous n need for us.

  6. / Reply

    I am very interested in your post. The information in your post is very benefitable for me. Thanks for share this post.

  7. / Reply

    this is an awesome blog. keep it coming!

    • Hakim,
    • August 10, 2012
    / Reply

    This is really nice article. It should also explain with examples so that it will become more effective.You have done a great job . well done.

    • Bikash,
    • February 10, 2013
    / Reply

    Good blog, thanks.

    • Phu Hiep DUONG,
    • April 27, 2013
    / Reply

    This is my experience in design & modeling complex Database (with more than 100+ tables and many relationship..):

    http://www.slideshare.net/duongphuhiep/vectalis-modeling-style

    1) The main idea is keep use only well-know structural patterns (tree, singleton, inheritance..) so that we can avoid to add odd columns or relation that nobody would understand!

    2) We did not need any costly tool to draw complex database, In the slide, I show how to use PowerPoint to make Searchable (Traceable) model drawing.

Leave a Reply

Your email address will not be published. Required fields are marked *

Deals

Iconfinder Coupon Code and Review

Iconfinder offers over 1.5 million beautiful icons for creative professionals to use in websites, apps, and printed publications. Whatever your project, you’re sure to find an icon or icon…

WP Engine Coupon

Considered by many to be the best managed hosting for WordPress out there, WP Engine offers superior technology and customer support in order to keep your WordPress sites secure…

InMotion Hosting Coupon Code

InMotion Hosting has been a top rated CNET hosting company for over 14 years so you know you’ll be getting good service and won’t be risking your hosting company…

SiteGround Coupon: 60% OFF

SiteGround offers a number of hosting solutions and services for including shared hosting, cloud hosting, dedicated servers, reseller hosting, enterprise hosting, and WordPress and Joomla specific hosting.