Learn how relational databases organize data into tables with rows (records) and columns (fields). A library database example shows how the Patrons table uses cardnum as a unique identifier, and why splitting data across related tables prevents duplicate records and keeps information clean.
Chapters
Now that we know the basic organization of a database, let's take a closer look at the main building block of databases. Tables. We saw in the previous lesson that databases are organized into tables, which hold related data about a particular subject. As we've seen, tables are organized into rows and columns. In the world of databases, rows are often referred to as records and columns as fields. A table's fields are limited to those set when the database was created, but the number of rows is unlimited. Let's talk a little bit more about table naming. Table names should be lowercase and should not include spaces. We use underscores in place of spaces, and ideally a table name would refer to a collective group, like inventory. But it's also okay for the table to have a plural name, such as products. A record is a row in a table. It holds data on an individual observation. Taking a look at the Patrons table, we see that the table has four records, one for each of the patrons. The record for Yasmin indicates that she became a member in 2022 and owes $2.05 in fines. A field is a column in a table. It holds one piece of information about all observations in the table. The Name field in the Patrons table lists all of the names of our library patrons. Because field names must be typed out when querying a database with SQL, field naming is important. Generally, field names should be lowercase and should not involve spaces. A field name should be singular rather than plural because it refers to the information contained in that field for a single record. This is why our table has cardnum and name fields rather than cardnums and names. Similarly, two fields in a table cannot have the same name. Finally, field names should never share a name with the table they are housed in so that it's clear in all cases whether a field or table is being referred to. A unique identifier, sometimes called a key, is just what it sounds a unique value which identifies a record so that it can be distinguished from other records in the same table. This value is very often a number in the Patrons table. It makes sense to use the cardnum field as the unique identifier for each patron, not the Name field, because it's possible that as our little library grows, two patrons might have the same name. Having more tables, each with a clearly marked subject, is generally better than having fewer tables where information about multiple subjects is combined. Take a look at the Patrons and Checkouts tables. Now, here's what our Patrons and Checkouts tables would look like if we tried to combine them. It's the same data, but much less clear because it now contains duplicate information. While we can see that Izzy has two checkouts and Maham has none, the card num column is no longer unique because of Izzy's multiple checkouts. We can always use SQL to gather information from multiple related tables and connect them if a question requires it, but table topics should remain se.
