Get Adobe Flash player

Intro to SQL Database Design

Intro 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  foreign keys 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. This 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-verse. 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-verse. 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. For Zero to Many, there may be instances in the second entity that do no have corresponding instances in the first entity. You do not see this in RDBM because it violates referential integrity. Lastly the One to Many is the most common relationship, for each instance in the first entity; there can be one or more instances in the second entity. However, for each instance in the second entity, there can be only one instance in the first entity (i.e. contacts and zip codes!). The primary key on the first entity usually becomes a foreign key attribute on the “many” side, although the foreign key could point to one of the alternate keys on the “one” side.

Normalization is the process of creating a database that follows specific rules. There are 3 forms of normalization that all database designers should follow. The first Normal Form is when a table or entity does not have any repeating information (i.e., Skill1, Skill2, etc). Also all the attributes should describe the table or entity. The second Normal Form is when there is a primary key that identifies the table or attribute and all other non-key fields or attributes depend on the primary key. The third Normal Form is removing any data that is not dependent on the primary key. If a designer follows these rules of normalization, they should be creating proper tables.

So in order to create a good database design, you have to know what information you want to store in your database and how you want to retrieve that data. You have to understand what are the components of a database, such as your primary and foreign keys and your database tables.

Defender USA