1. Creating tables
Now that you feel confident about your ability to create databases in PostgreSQL, we can move on to discuss the creation of an important organizational layer for your data. The object that I am alluding to is the PostgreSQL table.
2. The database table
Tables are where our data actually lives within the database.
Tables consist of a set of rows and columns as you might be accustomed to seeing in a spreadsheet program.
Columns are also called fields and have a specific data type.
Each row in the table is a record with the value for each field being specific to that particular record. Tables can consist of a large number of records depending on the amount of data that needs to be stored.
3. The CREATE TABLE command
The act of creating a new table in a database is performed by using the CREATE TABLE command.
The CREATE TABLE command is followed by a name unique to our database. Following the table name are parentheses which contain a list of field names and data type pairs separated by commas. Optional constraints can be specified after the data type specification. The CREATE TABLE command is terminated with a semi-colon.
Like database names, table names are restricted to a maximum length of 31 characters by default and can only begin with a letter or underscore.
4. Example table 1
Let's look at a couple of examples of using the CREATE TABLE command. Recall the previous example of a database for tracking NCAA basketball statistics. One table that would likely need to be created for this database is one to hold records for colleges and universities that compete in NCAA sports. This table can be named "school".
A command to add the school table to the "n-c-a-a underscore b-b" database is displayed here. An id field is defined to identify each school, a text field for the school name, and another text field for the school mascot. A NOT NULL constraint is included to require a name value for each record.
5. Example table 2
Another database that we discussed in the previous lesson was one for podcasts. An important table for this database might be one used to categorize podcasts. We can name this table "topic".
Despite its importance for representing the subject matter discussed on the podcast, the actual table structure could be quite simple including one field to hold the value of a unique identifier and a text field representing a description of the topic. The description field has a NOT NULL constraint in order to ensure that every record defining a topic contains a description. The records in this table would likely be associated to podcasts using a FOREIGN KEY.
6. Table organization
At this point, you may be thinking: "It is great that I now know how to issue the command to add a new table to my database, but I still have gaps in my knowledge that need to be filled regarding which tables to add". Specifically, you may be wondering:
Which fields should I include in my table?
How many tables should I include in my database to most effectively represent my data?
Which data types should I choose for my table fields?
These are all great questions. These questions are so important that we will devote large portions of the next couple of chapters to address them. The first two questions will be answered in chapter 3 of this course and the last question will be answered in chapter 2.
7. Let's practice!
Now that we have seen some examples for creating new database tables, we'll practice using the command in the following problems to solidify this knowledge.