Relational Databases for Archaeology Main Menu

Fields and Data Types in Tables

4
You may have multiple different databases in your PostgreSQL server.  In each database, tables are collected into groups called schemas.  You can consider schemas somewhat similar to folders on your harddrive that contain multiple files.  A schema can contain multiple tables and a table exists only within a single schema.  Note that the schema structure is specific to PostgreSQL, and may not be something you see in other database software.

Initially, we will only work in the 'postgres' database, and only create tables in the 'public' schema, which are the defaults.  If you wish to create additional databases, you may right mouse click on the 'Databases' icon and select 'Create' - 'Database ...'.  Similarly, if you wish to create a new schema within a database, right click on the 'Schemas' icon and 'Create' - 'Schema...'.

You may give any name to your databases, schemas, tables, and fields, however there are some suggested conventions that will prevent problems.  You will use other software to interact with your database, and these software programs may not be able to deal with some characters you want to include in your names.  It is best to avoid using spaces, punctuation, and capitalization in your names.  The table name 'Pottery & Lithics#' is allowed but is not recommended.  Use 'pottery_and_lithics_numbers' instead, substituting the underscore character for the spaces.

Here is what the schemas and tables look like in the Pgadmin view of PostgreSQL.  Take note of the 'postgres' database and the 'public' schema in the hierarchy.  The columns in the 'areas' table are also visible.  Although you will see many other things in the hierarchy here, the majority of these settings you will never need to use, as they provide functionality that a typical archaeological project would not need.

This section will mainly focus on the main building blocks of the table – the fields.  As we learned previously, tables are composed of columns, called fields, and each field stores a certain type of data.  We might have a field containing the decimal numeric value of a rim diameter of a bowl, or a text field that stores a shape value of 'bowl'.  Create a field whenever you have another piece of data you wish to collect for each row.  Some fields should contain unique identifying information for each row, the keys we will discuss later.

Here we will create our first table and add its fields.  Each archaeological project uses a slightly different way of naming and structuring its data, and for this guide we will follow the system outlined in Roosevelt, Cobb, et al (2015).  That recording system was designed from the start for direct-digital recording in a relational database.  The top spatial recording element is the area, this is a square trench excavated on a site.  Here we will create a table to record all of the areas at a site.

We will create a new table in the public schema of our postgres database by right mouse clicking on the ‘tables’ icon and selecting ‘Create’ … ‘Table…’.  Name the table ‘areas’, and proceed to the ‘columns’ tab where we will create our fields.  The first two fields we will create are ‘area_easting’ and ‘area_northing’.  These are the Universal Transverse Mercator (UTM) coordinates of the southwest corner of each area, simplified to include only the three digits from the thousands to the tens place.  These are x, y coordinates in the unit of decameters, that give a standard location of the trench on the ground, while at the same time providing a unique name for the area.  These two fields are integer numbers, since they do not contain digits after the decimal point.

To create a new field, press the + button on the right side of the columns tab.  Enter the name of the field, and then select the data type.  Ignore the other options for each column, for the time being.  In addition to the area_easting and area_northing, we will add four more fields: a character varying 'description', that will provide a brief text description of the area, a date for the 'date_opened', when the area was first excavated, a numeric 'elevation_top_meters' to record the topmost elevation prior to excavation, and a boolean for 'is_active', which indicates if the area is currently being excavated.  The following screencast demonstrates all of these steps.  Should you receive a message about the table missing a primary key upon saving, you can simply ignore that for now.


You will notice as you are creating the fields, that you have many options for data type.  An archaeological project would rarely have a need to use more than a few of these types.  The usual data types you would use are the ones we have already created: integer, character varying, numeric, date, and boolean.  Let us take a closer look at these types here.  For complete details, you can also refer to the PostgreSQL documentation.


Exercise: Create a ‘contexts’ table and a ‘samples’ table.

For this exercise, you should first create the ‘areas’ table as shown above, then you will create the ‘contexts’ and ‘samples’ tables as described below.  You will create these tables on your PostgreSQL database server, in the default database and schema.  You will determine how to name the fields and which data type to assign to each field, following the instructions discussed above.

The ‘contexts’ table records data about each context excavated within the area.  Contexts are identified first by reference to their area, so you will need to add the area_easting and area_northing fields into this ‘contexts’ table.  Within each area, each context receives a running number, starting from 1, this field is called a ‘context_number’.  These three fields uniquely identify each context and indicate to the archaeologist the location of the context on the site.

We would also like to record additional information about the context: 
The ‘samples’ table records data about all the material found within the context, such as pottery sherds or lithic tools.  Samples are identified first by reference to their area and context, so you will need to add the area_easting, area_northing, and context_number fields into this ‘samples’ table.  Within each context, each sample receives a running number, starting from 1, this field is called a ‘sample_number’.  These four fields uniquely identify the sample and indicate to the archaeologist where the sample was found.

We would also like to record additional information about the sample: 
In a later lesson, we will learn how to export the database from PostgreSQL in order to share it with someone else.
 

This page has paths:

  1. Relational Databases for Archaeology Peter Cobb

This page references: