Table Relationships =================== As mentioned briefly in the last chapter, we want to keep the design of our database tables *clean*. Each table should deal with a very narrow set of data, and any specific piece of information should be stored in ONLY ONE PLACE. This makes it much simpler to perform updates, since changes can be applied once rather than over multiple tables. For example, a school might keep track of their course offerings by creating a ``courses`` table. This would include information such as an ID number, title, duration, a short description, and number of credits. All of these items define a course---Chemistry, for example. Every course has an assigned teacher as well as a student roster, and these may also seem like good things to add to the ``courses`` table. However, a list of student names does NOT define "Chemistry". Since student placement in a course is temporary, the roster data needs to be kept in a separate location. Similarly, multiple teachers could run the same course. Since there could be two Chemistry classes that take place during 1st period, the names of specific instructors should be kept out of the ``courses`` table. A separate ``teachers`` table would include information like ID, name, email, and hire date---everything the school needs to define an instructor. Data such as courses taught, planning period, or room number would NOT go into this table, since these items can change every year and are not specific to one person. Similarly, a ``students`` table could contain ID, name, email, courses completed, parent phone numbers, and grades---anything that defines a particular child. Something like the name of their first period teacher would be stored elsewhere. So, ``courses`` only holds data that directly defines each class the school offers. ``teachers`` only contains information that defines each instructor, and ``students`` stores student data and nothing else. This sounds obvious, but it is very easy to throw lots of data into a table and provide descriptive column names. While this may seem like a good idea at the time, it leads to repetition of data, and it makes maintaining that information more difficult. Any time you add a new column to a table, ask yourself whether the data it represents *defines* that table. If you answer *No*, then store the data in a different place (e.g. ``lunch_period`` does not fit within ``students``, so it will have to go in a different table, such as ``schedule``). Relating Data ------------- Keeping data in separate, smaller chunks is more efficient than keeping a huge amount of data in one place. However, this often means we need to access information stored in different tables. To gather all the data we want, we will have to combine parts of these tables to get the whole picture. For our school example, knowing which students are enrolled in which classes is useful. Similarly, we might want to display a list of students shared by a group of teachers. *Relational databases* allow us to link tables together. Even though teacher names are not stored in ``courses``, a connection can be made between that table and ``teachers``. Inside ``courses`` we can add an ``instructor`` column. However, this column will NOT hold any names. Instead, it will store references that point to the ``teachers`` table. These references identify the information in ``teachers`` that is connected to a particular entry in ``courses``. .. _one-to-many-sql: One-To-Many Relationships -------------------------- .. index:: ! one-to-many relationship A common structure for database tables is the **one-to-many relationship**. In this form, each entry in one table relates to many rows in a different table. For example, a single row in ``courses`` represents one class offered by a school. That single course can be taught by multiple teachers, and lots of different students will be enrolled in it. Thus, *one* entry in the ``courses`` table relates *to many* entries in ``teachers`` and ``students``. .. admonition:: Note For another example of the one-to-many concept, feel free to read `this article `__. Table Keys ---------- To connect the ``courses`` table to ``teachers`` in a one-to-many relationship, the following conditions must be met: .. _foreign-key: .. index:: ! primary key, ! foreign key #. Each table must include a **primary key** column. A primary key is a unique, numerical identifier given to an entry. #. The ``teachers`` table must include a **foreign key** column. Foreign keys are integers that tie directly to the entries in a different table. In our school example, the foreign key for a row in ``teachers`` matches one primary key in ``courses``. Note that different teacher entries could have the same value for the foreign key. This sets up the one-to-many link between a single row in ``courses`` and multiple rows in ``teachers``. Adding a Primary Key ^^^^^^^^^^^^^^^^^^^^ In MySQL, the syntax for creating a primary key column is: .. sourcecode:: SQL column_name INTEGER PRIMARY KEY AUTO_INCREMENT ``column_name`` usually involves the term "id", and ``AUTO_INCREMENT`` ensures that each entry gets assigned a different integer value. For our school example, the code to create ``courses`` could look like: .. sourcecode:: SQL :linenos: CREATE TABLE courses ( course_id INTEGER PRIMARY KEY AUTO_INCREMENT, course_title VARCHAR(40), course_minutes INTEGER, course_description TEXT, credits INTEGER ); Adding a Foreign Key ^^^^^^^^^^^^^^^^^^^^ The general syntax for creating a foreign key column is: .. sourcecode:: SQL :linenos: column_name INTEGER, FOREIGN KEY (column_name) REFERENCES other_table(primary_key_column) Line 2 establishes the relationship between two tables. One way to interpret the line is, *The value for 'column_name' in this table relates to the entry in 'other_table' that has a matching primary key*. For our school example, the code to create ``teachers`` could look like: .. sourcecode:: SQL :linenos: CREATE TABLE teachers ( teacher_id INTEGER PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(40), last_name VARCHAR(40), email VARCHAR(80), hire_date DATE, course_id INTEGER, FOREIGN KEY (course_id) REFERENCES courses(course_id) ); Note that in line 7, the name given to the foreign key column in ``teachers`` matches the name of the primary key column in ``courses``. Following this convention helps you keep the relationships between your tables clear. .. admonition:: Note In the :ref:`last studio `, you established a relationship between a ``directors`` table and a ``movies`` table using a foreign key. Keys Wrap-Up ^^^^^^^^^^^^ #. Define a primary key column for each table in the database. #. Define a foreign key column for any table that fulfills the *many* role of a one-to-many relationship. #. The foreign key of the *many* relates to the primary key of the *one*. .. admonition:: Tip Even if you do not think a table needs one, define a primary key column anyway. Your database needs to grow and adapt to change. Adding a primary key column to a table at the beginning helps with that. Other Relationships ------------------- Besides the common one-to-many structure, there are two other ways to relate tables to each other. Read the following articles (or do a quick Google search) to explore these options: #. `One-to-one `__ #. `Many-to-many `__ You will need this information to answer the last few concept check questions. Check Your Understanding ------------------------- .. admonition:: Question Which if the following is the BEST table to store the period and room number for an Algebra I course? a. ``teachers`` b. ``students`` c. ``courses`` d. ``schedule`` .. Answer = d (schedule) .. admonition:: Question Which type of relationship exists between a ``dresser`` table and a ``drawer`` table? a. One-to-one b. One-to-many c. Many-to-many .. Answer = b (one-to-many) .. admonition:: Question Which type of relationship would exist between the ``teachers`` and ``students`` tables? a. One-to-one b. One-to-many c. Many-to-many .. Answer = c (many-to-many) .. admonition:: Question For the ``closet``, ``shelf``, and ``box`` tables, what are the relationships? (Don't overthink this). a. One-to-many and one-to-many b. One-too-many questions c. Many-too-many-too-many questions d. Um, wait, but... NOOOOOOO! I overthought it! .. Answer = a (one-to-many and one-to-many)