This is the fourth blog in our database design series. I wanted to create this one to sum up what we have learned in the previous blogs to prepare you for the blogs to come. I will also be introducing a few new terms that are important to know. Some words could fit in multiple categories, but you’ll get the idea. if you need an in depth explanation of a term check my other blogs or leave me a question in the comments.
I have categorized all of the terms we have used into these categories:
- Relational Database
- Database Management System
- Relational Database Management System
- Referential Integrity
Data is the anything that we can store in a database. Be creative. The actual database is a file structure that stores information on a hard drive but presents it to us in a structured fashion. A database provides much more flexibility and functionality. A specific kind of database is a relational database. A relational database uses multiple relations (tables), or categories of information. With a relational database you don’t just have a huge list of data, you have data that is structured in a way that is understandable. On top of databases we have database management systems (DBMS). These allow us to manipulate our data in a variety of ways…either adding data, deleting, searching, analyzing, protecting, and so forth. a DBMS also adds features such as security, multiple users, back ups, and more! A specific kind of DBMS is a relational database management system. A RDBMS works specifically with a relational database.
One of the main goals of a database is to prevent anomalies, repeating data, and unnecessary nullness (is that even a word?). An anomaly is anything that is abnormal, unwanted, or unexpected. Null is the equivalent of having no value. When describing things that are null, you will often hear “null value,” which is technically incorrect because by definition null = no value.
In order to removing possible anomalies, repeating data, and nulls, we must design our database in the best way possible. See the next section for design terms.
Referential integrity is when a database has been programmed to prevent key links from being broken or incorrect (see keys in next section).
- Database Design
- Naming Conventions
When designing a database, we first define all of our entities and attributes for each entity. An entity is anything we store data about in our database and attributes are the things we store about the entity. The entity-attribute connection is stored within a table (relation). The attributes are stored as columns. It can be a bit confusing, but look at this picture to visually see what I mean:
When we have this structure, the next thing we do is insert values, or real world answers to each of the columns. When we have put all of these values in, we have what is known as a row(tuple):
Here you can see real-world data stored within our database. “352345, CalebCurry, sharemystuffwithfriends” is our first row within our database.
The terms “relation,” “tuple,” and “attribute,” are the fancy technical terms of database design. The term “attribute” is used often because it easily explains our data… “Username is an attribute of a user account,” is sometimes more useful than: “Username is a column within the user account table.” This is because we may not be to the point yet of building tables and instead are just designing what we need to store within our database. This table might be useful to understand which terms are talking about the same thing:
You may also hear the older terms “file,” “record,” and “field,” which are the equivalents of “table,” “row,” and “column.”
|Formal||Common informal||Old/less common|
In the process of inserting data in our database, you may hear the term “entry,” which refers to entering a new row.
There are a lot of terms to familiarize yourself with, but it is important that you know them all!
Once you understand the terms and structure of a table, we start looking at database design as a whole. Database design is the process of planning our database before we actually program it. This includes everything from individual table structure, table relationships, keys, data types,character sets, storage engines, securty, and so on. The first thing we do is create the database schema, or table structure. This includes all of the tables as well as the relationships between tables. Once we have a general schema, we normalize our database to remove potential anomalies. Typically we follow a naming convention to keep things consistent. A naming convention is just a pattern used for naming our database, tables, rows, columns, and also how we draw out our database plans.
Keys, although we have barely talked about them, are used to connect tables. You’ve probably noticed that most of my tables shown so far have an ID column. This ID column is a key to keep each individual row unique. We can also reference this key in another table. When we do this, we have a bunch of tables that are all connected. This is important because it keeps our entire database up to date and protects us from incorrect data.
- SQL Keywords
SQL is the programming language used to create, edit, manipulate, and destroy relational databases and the content within them. SQL is broken down into two categories, DDL, and DML. DDL is used for the database structure while DML is used for the database data. SQL keywords are words that are reserved for SQL use and we should never try to name something an SQL keyword. SQL keywords are usually represented in all capital lettes.
- Front End
- Back End
- Client Side
- Server Side
- Server Side Programming Language
Front end is the part of an application that is shown to the public or to specific users. Often times a database will be used with a front end system that communicates to the back end. Doing this allows further customization and security. The back end is the actual behind-the-scenes programming languages that (in some cases) should never be shown to the public. Client side is another common name for front end and server side is another common name for back end, referring specifically to the code that is stored on a server verses the code that is sent to the client. A server side programming language (or server side scripting language) is a language used to program the back end of an application that is stored on a server. SQL is an example of a server side programming language.
Be sure to check out the next blog to start the next level of database design!