08) Database Schema

Database Schema

Schema: A database schema Links to an external site. is the initial blueprint or design that will properly store all of our data and link disparate types of data in related database tables.

Database Schema Videos: The following are videos designed to introduce the concepts behind building your database schema. I recommend you view them in order:

Expect to be tested on the content found in these videos!

Identify Entities: The factors involved in building the database to store all our data begins with identifying all the 'entities' (pieces, parts, processes & players) that may require storage in our database. Entities can be thought of as nouns. Examples: a computer, an employee, a song, a mathematical theorem.

When we create our initial Database Schema (Design Links to an external site.) we can first think of every piece of the process as a candidate for being it's own table:

We'll be creating surveys
The surveys will have questions
The questions will provide possible answers
The surveys will generate responses

Each of the above entities are different enough to be considered candidates for separate database tables. Once we identify our initial set of entities/tables, we decide if they have a one to many relationship between the entities:

A survey can have many questions
A question can have many answers
A survey can illicit many responses

Later we can draw an Entity Relationship Diagram (ERD, see below) to help us visualize the data.

Identify Attributes: Once we have entities, we must also define what type of information we need to store about our entities. We use the word attributes to define the qualities or data we need to store about each entity. For example, if we had an entity named Customers, some attributes we may need to store are FirstName, LastName and Email. Later the entities will become tables and the attributes will become the fields. Later still, when we write our program, the entities will become objects and the attributes will become properties of the objects. Therefore:

Entities == Tables == Objects
Attributes == Fields == Properties

Next we'll start to transform our entities & attributes to tables & fields!

Separate Database vs. Separate Tables: It is good to ask if we should use a separate database, or just use a set of tables in our existing database for our application. Unless we have an entire separate set of data (for example a database dedicated to search) we should always use a single database in order to link the tables together.

Table Naming Convention: For this assignment we'll be prefixing all database tables with a prefix that will allow us to recognize all of the tables involved in the application. The shared prefix also allows code to change the prefix so our tables don't conflict with the tables of another application, since table names in a database share the same namespace and therefore must be unique. A good prefix for this app might be "srv_". In this class we'll use a prefix to designate the quarter, for example for the Winter of 2014 the prefix will be wn14_.

Going with our previous example, if srv_ is our prefix and we had a questions' table, then, we could name it: srv_questions

Note the plurality, 'questions'. If a DB table stores data that singularly would be a 'question' the plurality 'questions' becomes a logical choice to identify a table.

If we were going to link another table, for example, a table of answers, named 'srv_answers', via a 'link table', the name could then be: srv_questions_answers

Entity Relationship Model: It helps to have a model to view our database tables visually. Modeling the database in this way results a visual Entity Relationship Model Links to an external site. . The diagram created to represent the model is called an Entity Relationship Diagram, or ERD. Below is a simple example of an ERD:

This model helps us visualize the relationships between the tables. The unique Primary Key Links to an external site. of one table appears in a related table as a Foreign Key Links to an external site.. We'll link the tables together temporarily via these keys to produce results that we'll use in our application.

Free ERD Tools: There are lots of DB modeling tools. Here are a few:

Identify Table Fields: Next we'll need to identify the possible fields for each of the tables. Examine all entities identified earlier as potential fields. Fields/entities help define and store qualities of our data items. For example, fields for a Customers table might be FirstName, LastName and Email. The number and types of the fields are based on the needs of the application.

All Tables Should Be Joined: By default, assume that all tables in our application must be joined via a primary/foreign key combination. Any table that stands alone should be re-examined for relevance or for connectivity to the existing tables.

Starter SQL: In order to build your SQL file, start with the following example that links the Surveys table to the Questions table. All other tables you build should link via a primary/foreign key relationship: Starter SQL Links to an external site.

DB Concepts Found in Starter SQL: Below are some items we need to be aware of when working with our database table:

INDEXES Links to an external site.: Any field that has highly unique data that is either searched on or used as a join should be indexed, which speeds up a search on a tall table, but potentially slows down an add or delete

You'll see indexes below for example:

INDEX SurveyID_index(SurveyID)

TIMESTAMP Links to an external site.: MySQL currently only supports one date field per table to be automatically updated with the current time. We'll use a field in a few of the tables named LastUpdated:

LastUpdated TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

The other date oriented field we are interested in, DateAdded we'll do by hand on insert with the MySQL function NOW() Links to an external site..

CASCADES Links to an external site.: In order to avoid orphaned records in deletion of a Survey, we'll want to get rid of the associated Q & A, etc. We therefore want a 'cascading delete' in which the deletion of a Survey activates a 'cascade' of deletions in an associated table. Here's what the syntax looks like:

FOREIGN KEY (SurveyID) REFERENCES srv_surveys(SurveyID) ON DELETE CASCADE

The above is from the Questions table, which stores a foreign key, SurveyID in it. This line of code tags the foreign key to identify which associated records to delete.

Be sure to check your cascades by deleting a survey and watch all the related table data disappear!

MySQL Table Types Links to an external site.: The type of table we use to build our tables is significant in MySQL. The two main table types currently in use are as follows:

MyISAM: This is usually the default table type when no table type is specified. MyISAM supports full text search. MyISAM does not support constraints or foreign keys. My ISAM does not support transactions. MyISAM takes less disk space than InnoDB. MyISAM can be operationally faster than InnoDB in certain circumstances.

InnoDB: This table type supports foreign keys and constraints. It also supports transactions while MyISAM does not. InnoDB can be a bit slower than MyISAM and doesn't support full text search. InnoDB also takes up more disk space than MyISAM.

Our Selection, InnoDB: You may notice a bit of code at the bottom of each table creation statement:

ENGINE=INNODB;

We're specifying InnoDB as our table type of choice for each table involved in our application. This is because we'll be performing transactions Links to an external site. later, in which several SQL statements must be run successfully to be deemed complete. A failed transaction then can be rolled back and data potentially repaired or erased to simulate the state before the failed transaction was run.

The other reason is foreign key constraints are supported in InnoDB, which will help us when deleting data so we don't leave behind any orphaned records Links to an external site. (database data for which no key in a related table exist)

Easy ERD: Once you create your tables successfully by running your SQL command in Adminer, click on Database Schema

You'll see all your tables listed in alphabetical order. Grab and drag them and you'll see the relationship lines are visible as well! Position them on the screen in a way that makes your related tables visible, but hides the unnecessary or related tables. Take a snipping tool or screen shot and you're done!