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.
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.
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, suchas MySQL Workbench (for MySQL databases only) or DBDesigner4.Gliffy is also a great free online application for
creating flowcharts and database models.
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 relationaldatabases, 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.
Every entry in every table needs a uniqueprimary 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.
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.
FURTHERRESOURCES
§
Database
Design Tutorial
§
10
Common Database Design Mistakes
§
Introduction
to Database Design
§
Principles
of Good Database Design
§
7 Steps to a Good
Database Design
