1. Tables
Now that we know the basic organization of a database, let's examine its main building block: tables!
2. Table naming
Let's begin with table naming.
A table name should be clear and refer to the data it contains, like inventory, products, or books.
Also, table names should be in lowercase and use underscores instead of spaces.
3. Records and fields
In the previous video, we saw that databases are organized into tables, which are organized into rows and columns; in the world of databases, rows are often referred to as records and columns as fields.
4. Records
Each record contains the data for an individual observation.
Here, the patrons table contains four records, one for each patron.
The record for Jasmin indicates that she became a member in 2022 and currently owes $2.05 in fines.
5. Fields
Fields contain one piece of information for every record in a table.
For example, the name field in the patrons table contains all our library patrons' names.
6. Field naming
Field naming is important because field names must be typed out when querying a database with SQL. Similar to table names, field names should
be lowercase and
use underscores instead of 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 "card_num" and "name" fields rather than "card_nums" and "names".
Finally, two fields in a table cannot have the same name, and they should never share a name with the table they are housed in so that it's clear whether a field or table is being referred to.
7. Unique identifiers
Tables include a special field containing a unique identifier for each record, sometimes called a key. The key value must be unique for every record.
In the patrons table, the card_num field is the unique identifier for each member, as opposed to the name field, because it's possible that two patrons might have the same name as our little library grows.
8. Multiple tables
Using several, well-organized tables is often better than putting everything into one big table.
For example, if we combine our patrons and checkouts tables, the information can get confusing. 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.
By keeping data in separate, related tables and connecting them with SQL, we can analyze and answer questions more easily than we could with spreadsheets.
9. Let's practice!
We'll learn more about that soon. For now, it's time to practice your table knowledge.