The structure of a database table - PostgreSQL Tutorial

- [Narrator] In order to get the most out of Postgres we need to have a good understanding about how relational databases are structured. The foundation of any database is a data table. Data tables are used to organize and store information that you want to keep track of. As we saw in our first explorations of Postgres you create a data table by defining the different columns that it's composed of. In database terminology these columns are often called attributes or fields and they're used to store individual descriptive components of each item that'll eventually be stored in the table. For instance, in a table about books you might have a column for the title author and publication year. Notice the conventions that I'm using here for the column header names. In Postgres databases, it's the standard to use all lowercase letters for all table and column names and to separate multiple words with an underscore character. It's not strictly required, but it's the format that almost every Postgres developer follows. So it's best to stick with the general practice when developing your own databases. Now, once you have your data table created you can start adding in rows of data. Each row in the table will represent a single item. If you think of the columns as descriptive adjectives then each row are the nouns, the people, places, things or events that you're storing information about. In this case, each row is an individual book. Each additional row in the data table is information about yet another book. When it comes time to read data back out of the table it'll be very important that we can uniquely identify each row in the table. In this table about books, you could simply ask for the book by its title, but if I were to ask you for the book published in 1847, you wouldn't be able to precisely identify which single book I meant since there are two that meet that criteria. And as we add data to this table, it would be pretty common to have multiple books by the same author. Eventually, this table might even grow large enough to actually have two books with the same title. So we can't really rely on the title to always uniquely identify each row in the table. For this reason, we need to add in one additional column to this table's design. The standard convention for this column is to name it with the name of the table followed by ID. This will store a unique identifier for each row in the table. So if I asked for book ID number three that will always point to this one specific edition of "Wuthering Heights" by Emily Bronte, published in 1847. The book ID column is called the table's Primary Key. A table's primary key column always has a unique constraint applied to it. It's only job is to make sure that every row on the table always has at least one piece of information that's different from all other rows. Often this column doesn't have any real world significance. The fact that the time machine was book ID number one and Jane Air was book ID number two doesn't have any meaning outside of the database. It's not a ranking or imply any sequence or give you account of how many items are in the table or have any other significance other than to say that book one is a different thing from book two. That's all a primary key does. If you think about your own life you'll find that primary keys can be found all over. Your credit card number, for example, is a primary key in your bank's database that uniquely identifies your card from every other card in the world. Your library card number, social security number, phone number, email address all of these are primary keys in someone's database and all of those values point to you as a unique individual. So that's the structure of a database table. When creating them, you'll define the specific purpose that each column will have and set it up so that columns will only store a single attribute about the items in the table. And be sure to include a primary key column so that every row can be uniquely identified as a separate entity from every other row in the table.