What are Relational Databases?
While working for IBM in 1970, E. F. Codd developed the idea of a relational model for organizing database structure, and coined the term “relational database” in his research paper “A Relational Model of Data for Large Shared Data Banks”. He imposed a definition containing a dozen rules (Codd’s 12 Rules), two of which are considered necessary today at a minimum:
1- (Relational databases should) present the data to the user as relations (a presentations in tabular form, i.e. as a collection of tables with each table consisting of a set of rows and columns);
2- Provide relational operators to manipulate the data in tabular form.
A database can contain as many tables (or “relations”) as needed to store and organize necessary data. Each table is comprised of rows and columns (also known as “attributes”) with a unique “primary key” identifying each row (which are also sometimes called records). When the row of one table is linked to a row in another, a column containing a unique id for this linked row is created, which is called a “foreign key”.
The most common relational databases are MySQL and PostgreSQL, which use Structured Query Language to access and manipulate data, using commands like "INSERT", "SELECT", "ALTER", and "DELETE". SQL is straightforward and doesn't take very long to learn.
Relationships Between Data
When thinking about designing a database, you must first decide which data needs to be stored, then determine the relationships between the data. In order to identify the relationships between tables, you must examine them from both sides. They will fall into one of the following categories:
One-to-One
Each row in the first table is related to only one row in the second table.
One-To-Many
Each row in the first table can relate to any number of rows in the second table.
Many-to-Many
Any number of rows in the first table can relate to any number of rows in the second table. This type of relationship does not work for tabular data, so a junction (or intersection) table will be necessary to break them up into two one-to-many relationship tables.
Building Tables
Imagine we're creating a MySQL database for our local veterinarian's office. Important information to store would include pets' names and breeds and their owners' names and phone numbers. We could set up a table called "pets" that will contain the pet names, species, and age and give each pet a unique id.
To keep our tables nicely organized, we can store a list of all animals' species in another table, assigning a unique id to each species type.
The relationship between the pets table and the species table is a many-to-one: each pet can only belong to one species, but each species can have many pets.
We'll make an owners table that will store the full names and phone numbers of the owners.
Each owner could have an unknown and potentially large number of pets, so it's not a good idea to directly store pet information in this table. Additionally, each pet could have more than one owner listed, creating a many-to-many relationship that necessitates a join table in which we'll enter the unique ids of each owner and pet.
Each field should only contain one value, or have a single source of truth. This information could look something like this on a spreadsheet:
Putting it all Together
A very helpful tool for visualizing data and designing tables for MySQL and PostgreSQL databases can be found at https://dbdiagram.io/. You enter the table names and contents into the input field on the left and it will generate those nice tables for you. Here is what I typed to create the tables shown above:
Each table has a column for id, which will be an integer (int) called the primary key ([pk]). "varchar" signifies a string datatype and is followed by a number in parentheses to specify the maximum length allowed, up to 8,000 characters. Other data types include date, time, binary, et cetera. The full schema for our imaginary pet/owners database looks like this:
You can show those relationships by dragging from field to field, and it will generate a list for you at the bottom of the code editor. (You can also type them in yourself if you prefer.)
< means one-to-many
> means many-to-one
- means one-to-one
They even allow you to export the schema you've designed, or import an existing schema you may want to examine visually.
Conclusion
Relational databases store information in organized tables.
Data is accessed and manipulated using SQL.
Visualizing schema structure can be challenging. Using a designer like dbdiagram helps to understand the relationships between data in order to create an organized configuration.
Top comments (0)