Introduction to Developing a Relational Database
Creating database tables and fields can be a fairly straightforward process and even intuitive with a few key steps.
Technical database developers tend to use terms like entities (of which come acronyms like ERD which is an Entity Relationship Diagram). I prefer using tables and fields. A table is your entity or your object and a field is a property of that object.
When first thinking of what the tables and fields should be, it?s best to think of everything as objects. For example, you have a site where users can log in. The user would be your object. The fields (aka properties) of your user could include username, password, and e-mail address.
Let?s take a blog, as another example. A
blog has articles and comments. Both an article and a comment would each be an object.
COMMENTS would be our tables. An article might have an author, a post date, along with the article content itself. Likewise, a comment might have an author, the author?s e-mail address, along with the comment itself. These will be the fields of our tables.
What?s a Primary or Foreign Key?
A primary key is a field that stores a unique value for each field in that table. A primary key is usually a number that automatically increments for each new record entered into the table to ensure that the field is unique. A foreign key is the primary key value stored in a secondary table and defines a relationship between the two tables. A primary key should also be indexed. Most databases automatically create an index on a field marked as the primary key. An index is a lookup system that the database server uses to find results faster.
Our user table, for example, would have a field
id. This is the primary key. If that user were to post a new article
on the site, to identify that user as having written that article, the id of
that user is stored in a record in the article table. This links that particular
article to that particular user. The
userid field in the
article table is the foreign key.
Table and Field Naming Convention
Those who have come from an Oracle database
development background tend to name their tables singularly. The table that
stores all the users would be the
USER table. My personal
preference is to actually name the tables plural. In this case, it would be
I also like to include a table prefix that relates to the project. This is often a good idea as it helps resolve any naming conflicts when tables for two different applications have to reside in the same database.
When it comes to field naming, I?ve noticed
that most keep it simple. Field name for our
USERS table would be
usremail. This tends to come in quite handy when joining a number of tables
together as I find it more obvious and can avoid having to include table names
when referencing my field names. I also like that my primary and foreign key
fields are the same name.
For example, let?s say we have a blog. Within
the database for this blog we have a
posts table and a
table. Each table has an
author field. If for some reason we needed to join these two fields
together, we?d see something like this:
SELECT posts.author, comments.author FROM posts INNER JOIN comments ON posts.id = comments.postid
Be using the naming convention I describe above, you could instead do the following:
SELECT pstauthor, cmtauthor FROM posts INNER JOIN comments ON posts.pstid = comments.pstid
I have steered back to the norm these days as most frameworks like Ruby on Rails tend to require the simpler naming convention and it just so happens to be what my co-workers use (things work out better when everyone on the team uses the same style). I?ll leave it up to you to decide which is easier to use. It is important to develop a process and stick with it for the course of a project.
Normalisation. What is it?
Normalising is the process of removing any
redundancies that may exist in a table. Let?s go back to our user. Now we need
to store additional e-mail addresses for each user (I can?t be the only one
with five e-mail addresses). While it may seem tempting to simply add on fields
email3, you can probably already see how difficult this would be to scale.
You might think to yourself, what user would ever have more than three e-mail
addresses? But by moving e-mail addresses into a table on its own, a user can
have as many e-mail address associated with his or her account as the database
We create an email table which contains our primary key, the actual e-mail address and then the foreign key for the user id to link an e-mail address to a specific user.
EMAILS id email userid
For an in-depth description of each of the forms of normalisation, I suggest you read the Wikipedia entry.
Sometimes, it may make sense not to
normalise, or rather, to denormalise some aspect of your database. Take a
one-time survey as an example. If the survey has 10 questions then the field
names could be
question10. Each record would store a user?s selected answers. Creating one
insert statement to store all answers is easier to manage than having to do an
insert for each answer. In this case, 10 different insert statements would have
to be executed).
If you have any tips that you'd like to share, feel free to add them to the comments.