Introduction to SQL Database Design
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.