Relational Databases for Dummies

Data can be inserted, retrieved, updated, and deleted from a table.

So far, Table 1 has been split into three new tables: following (Table 2), tweets (Table 4), and users (Table 5). But how is this useful? Repetitive data has been removed, but now the data is spread out across three independent tables. In order to retrieve the data, we need to draw meaningful links between the tables. This way we can express queries like, “what a user has tweeted and who a user is following”.

The way to draw links between tables is to first give each row in a table a unique identifier, termed a primary key, and then reference that primary key in the other table to which you want to link.

We’ve actually already done this in users (Table 5) and tweets (Table 4). In users, the primary key is the “username” column because no two users will have the same Twitter handle. In tweets, we reference this key in the “username” column so we know who tweeted what. Since it is a reference, the “username” column in tweets is called a foreign key. In this way, the “username” key links the users and tweets tables together.

Is the “username” column the best idea for a primary key for the users table?

On one hand, it’s a natural key — it makes sense to search using a Twitter handle instead of assigning each user some numerical ID and searching on that. On the other hand, what if a user wants to change his Twitter handle? That could cause errors if the primary key and all referencing foreign keys aren’t updated accurately, errors that could be avoided if a constant numerical ID was used. Ultimately the choice depends on your system. If you want to give your users the ability to change their username, it’s better to add a numerical auto-incrementing “id” column to users and use that as the primary key. Otherwise, “username” should do just fine. I’ll continue to use “username” as the primary key for users

Let’s move on to tweets (Table 4). A primary key should uniquely identify each row, so what should the primary key be here? The “created_at” field won’t work because if two users tweet at the exact same time, their tweets would have an identical timestamp. The “text” has the same problem in that if two users both tweet “Hello world,” we couldn’t distinguish between the rows. The “username” column is the foreign key that defines the link with the users so let’s not mess with that. Since the other columns are not good candidates, it makes sense here to add a numerical auto-incrementing “id” column and use that as the primary key.

Table 6: The tweets table with an “id” column

Finally, let’s add a primary key to the following table. In this table, neither the “from_user” column nor the “to_user” column uniquely identifies each row on its own. However “from_user” and “to_user” together do, since they represent a single relationship. A primary key can be defined on more than one column, so we’ll use both these columns as the primary key for the following table.

As for the foreign key, “from_user” and “to_user” are each foreign keys since they can be used to define a link with the users table. If we query for a Twitter handle on the “from_user” column, we’ll get all the users he follows. Correspondingly, if we query for a Twitter handle on the “to_user” column, we’ll get all the users following him.

We’ve accomplished a lot so far. We removed repetitions across columns and rows by separating data into three different tables, and then we chose meaningful primary keys to link the tables together. This entire process is called normalization and its output is data that is cleanly organized according to the relational model. The consequence of this organization is that rows will appear in the database only once moving forward, which in turn make the CRUD operations easier.

Fig. 4 diagrams the finalized database schema. The three tables are linked together and the the primary keys are highlighted.

Fig. 4

---

Relational Database Management Systems

There are small variations in SQL between each RDBMS vendor, termed SQL dialects.

Now that we know how to design a relational database, how do we actually implement one? Relational database management systems (RDBMS) are software that let you create and use relational databases. There are several commercial and open source vendors to choose from. On the commercial side, Oracle Database, IBM DB2, and Microsoft SQL Server are three well known solutions. On the free and open source side, MySQL, SQLite, and PostgreSQL are three widely used solutions.

MySQL is used at just about every Internet company you have heard of. In context of this article, Twitter uses MySQL to store their users’ tweets.

SQLite is common in embedded systems. iOS and Android let developers use SQLite to manage their app’s private database. Google Chrome uses SQLite to store your browsing history, cookies, and your thumbnails on the “Most visited” page.

PostgreSQL is also a widely used RDBMS. Its PostGIS extension supplements PostgreSQL with geospatial functions that make it useful for mapping applications. A notable user of PostgreSQL is OpenStreetMap.

---

Structured Query Language (SQL)

Once you’ve downloaded and set up an RDBMS on your system, the next step is to create a database and tables inside of it in order to insert and manage your relational data. The way you do this is with Structured Query Language (SQL), which is the standard language for working with RDBMSs.

Here’s a brief overview of common SQL statements that are relevant to the example Twitter data above. I recommend you check out the SQL Cookbook for a more complete application-driven list of SQL queries.

• Create a database, named “development”

    CREATE DATABASE development;  

• Create a table named “users”

    CREATE TABLE users (    full_name VARCHAR(100),    username VARCHAR(100)  );  

  RDBMSs require that each column in a table is given a data type. Here I’ve assigned the “full_name” and “username” columns the data type VARCHAR which is a string that can vary in width. I’ve arbitrarily set a max length of 100. A full list of data types can be found here.

• Insert a record (the Create operation in CRUD)

    INSERT INTO users (full_name, username)  VALUES ("Boris Hadjur", "_DreamLead");  

• Retrieve all tweets belonging to @_DreamLead (the Retrieve operation in CRUD)

    SELECT text, created_at FROM tweets WHERE username="_DreamLead";  

• Update a user’s name (the Update operation in CRUD)

    UPDATE users  SET full_name="Boris H"  WHERE username="_DreamLead";  

• Delete a user (the Delete operation in CRUD)

  DELETE FROM users  WHERE username="_DreamLead";  

SQL is pretty similar to regular English sentences. There are small variations in SQL between each RDBMS vendor, termed SQL dialects, but the differences are not dramatic enough that you can’t easily transfer your SQL knowledge from one to the other.

---

Conclusion

In this article, we learned how to design a relational database. We took a collection of data and organizing it into related tables. We also briefly looked at RDBMS solutions and SQL. So get started by downloading an RDBMS and normalizing some of your data into a relational database today.

Preview Image Source: FindIcons.com/Barry Mieny

Original Link: http://feedproxy.google.com/~r/nettuts/~3/T3lvJXmX0vg/

Share this article:  Tweet 

View Full Article

TutsPlus - Code

Tuts+ is a site aimed at web developers and designers offering tutorials and articles on technologies, skills and techniques to improve how you design and build websites.

More About this Source Visit TutsPlus - Code

Home - Sources - Contact - Advanced Search Copyright © 2011 - 2024 WebNuz. All Rights Reserved.