Introduction to SQL Database Design

By Jermain Gordon

A Database is the storage of data in what are called tables. Tables can be looked at as rows and columns. Databases can contain any number of tables; however designing the database requires thoughtful planning. The first step in the process it to know what data is going to be stored.

Without knowing what kind of data you are planning on storing, you will not know how to create the tables. A good designer will gather information to understand the data and the best way to store the data. This takes time and practice but a good start would be to understand what is being stored and how the stored data should be used. Start off drawing the database one table at a time.

A table is the place where the data is stored. Each table will have several columns to depict the specific attributes such as name or address, as would be expected in a table for storing contact information. It is always a good idea to draw out the table on paper to get a visual idea of how it should look.

The rows in the table are called records and will store data based on the columns data type. The data within each row are called attributes and are specific to that record. When designing a table, look at the data that will be presented. If you notice that some columns may have data that repeats, then that column is a good candidate for its own table.

For example, in a table that stores contact information, the state and zip code columns could repeat a state or zip code for several of the contacts. It would be a good idea to create a separate table to store zip codes and another table to store states. Refer back to these tables by using keys foreign and primary keys.

Every table should have its own primary key. This identifies the individual records in a table so that it can be quickly referenced. This is also necessary when making changes to data that should not affect the entire table but only certain records.

There are three types of table keys; primary keys, candidate⁄alternate keys and foreign keys. The first is the primary key. This attribute is used to identify a specific record and cannot be changed during the life of the record. Not only the value cannot be changed but the type cannot be changed either.

Next we have what are called Candidate and Alternate keys. These are attributes that also can be used to identify an entity in addition to the primary key. It's similar to having a backup primary key. Although not necessary, they provide additional ability to identify unique records. In a table that stores contact information this would be like having an ID field as well as an SSN field. Both attributes would be unique.

Finally, we have the foreign key. This is an attribute that completes the relationship between entities. A foreign key is used to maintain data integrity (called referential integrity). When we talked about the tables earlier, I mentioned that if columns in your table will have data that repeats, it should have its own table.

If each table contained a primary key as it should, then a table that contains zip codes or states, could be linked to the contact table by referring to the zip codes primary key value and not the zip code, in the contact table. This creates a relationship between the tables where the contact table requires the zip code or the state tables to exist to pull values from their tables. This also makes updating information easier.

How can you relate two tables? If two entities can be associated by using a verb, then a relationship exists. For example, Contacts have zip codes. Since contacts have zip codes we can create a table for zip codes and pull the zip codes into the contacts table. Each contact can have one zip code but each zip code can have many contacts. The type of relationship can be defined as one to many.

There are four types of entity relationship; One to One, Many to Many, Zero to Many, and One to Many. For a One to One relationship, each instance in the first entity has only one instance in the second entity and vice-versa. This is not a good way to design a table.

A Many to Many is when each instance in the first entity has one or more instances in the second entity and vice-versa. This cannot be handled in a relational database so you will have to create associated relationships. This is an intermediate relationship between the two entities. The primary keys of both entities become the attributes of this associative entity.

RSS Feed

 Follow @Stephen Bucaro

 Active Server Pages
 Java
 MySQL
 Visual Basic Programming
 XML
 Microsoft TechNet Home