ORDS How-To 04: Build and structure databases

1. About this guide

This document is one of a series of How-To guides for the Online Research Database Service (ORDS). It will tell you how to build a new database in the ORDS, and how to edit the structure of an existing one.

Please note that the ORDS is now in the process of being wound down. From 1 June 2017, the service will cease to be supported by the University of Oxford. We therefore strongly recommend that the ORDS is not used for new projects. Please contact the Research Support team to discuss alternative options.

Contents

1.1. Pre-requisites

This How-To assumes that you have already registered to use ORDS, and have already either created a project, or been given access to one as a project administrator.

For instructions on how to do this, see the earlier guides in this series How to get started using the ORDS and How to create and manage projects.

2. Creating a new empty database

2.1. Getting started

Project owners and project administrators can create ORDS databases. The first step is to create a new empty database in the ORDS system.

Log in to the ORDS via https://app.ords.ox.ac.uk, You will see a list of your ORDS projects. Click the name of the project in which you would like to create the new database to be taken to the project page.

If the project does not currently contain any databases, you will see a message indicating this:

No databases message

If the project already contains databases, you will see a list of these, with the Add new ORDS database button above it.

Click the Add new ORDS database button.

2.2. The Add Database to Project form

You will now see the Add Database to Project form. This allows you to enter some basic information (or metadata) about the database you wish to create.

Add Database to Project form

Two fields in the form are mandatory: Name and Description.

The database Name can be whatever you choose, providing it is at least two characters long. However, brackets should be avoided in databases names, as these can sometimes cause a system error resulting in data being lost.

The Description is intended to provide a summary of key information about the database. The field can be used to describe the subject matter and scope of the database, and to supply other relevant information, such as details of the people involved in creating or working on it, or how it relates to a larger project.

The database name and description will appear in the list of databases you and other project members see on the project page.

You can also supply a creation date for the database. This will often be the date on which you first add the database to the ORDS, but does not have to be: for example, if you are using the ORDS schema designer to recreate an existing database, the creation date may be some time earlier.

You can type the date into the box directly, using the DD/MM/YYYY format. If you do not wish to specify the date this precisely, you can add just the month and year (MM/YYYY), or just a year (YYYY).

Alternatively, you can select a date using the calendar. To do this, click the small calendar icon at the end of the Created on field.

Created On field with calendar icon highlighted

A calendar will appear. Use the arrow keys on the top row to move to the appropriate month and year, then click the date you want. It will appear in the field.

Created On field showing calendar

The two final fields in the form are Data sources and Data gathering process. These allow you to document where the data initially came from, and how it was collected. The amount of information it is appropriate to include here will vary from project to project (and for some projects, one field may be more relevant than the other), but it is good practice to record this information somewhere. This is especially important if the database will be made public or shared with other researchers at some point. You will be able to edit or add to these fields later if you choose.

When you are happy with the contents of the form, click Add to create the database.

You will be taken back to the project page, and should see a message confirming that the database has successfully been created.

The name of the new database will now appear in the list of ORDS databases for the project. Note that because this is as yet an empty database, the number of database versions is currently zero.

Database list showing empty database

Click the name to go to the database page.

3 Creating a new database structure

3.1. The database page

The database page displays your database’s details, and allows you to manage the database.

Database page for an empty database

ORDS allows you to create up to three versions of each database. Details of how to do this, and of what you might use each version for, can be found in the later guide, How to manage ORDS databases and database versions.

When you create a new database from scratch, this automatically becomes the main database version. In the lower left-hand part of the screen, you will see the Main database version information pane.

Click the Create new database button. You will be taken to the ORDS schema designer.

3.2. The ORDS schema designer

The ORDS schema designer is a graphical interface that allows you to build and edit a database schema, or structure. The schema will consist of tables, fields within those tables, and relationships between fields.

ORDS schema design page

The schema designer interface consists of three main components.

The working area occupies most of the screen, and is where you will create your tables and fields.

The toolbar is in the top right-hand corner. This can be hidden and unhidden by clicking the small arrow at the top.

The thumbnail map is in the bottom right-hand corner. This gives an overview of the whole working area – as you add tables, you will see small versions of these appear here. The part of the working area currently displayed on the screen is shown outlined in red.

3.2.1. Database design

If you are building a database from scratch, it is good practice to spend some time planning your data structure right at the beginning of the process; it is much easier to make changes at this stage than after adding data. You can do the planning either on paper, or by using the schema designer to experiment with different structures.

If you are new to relational database design, there are plenty of books and websites available on the subject. The University of Oxford IT Learning Programme offers a half-day introductory course on Concepts of database design. Oxford researchers can also contact the IT Services Research Support team for personalized advice. To arrange a meeting, email researchsupport@it.ox.ac.uk.

3.3. Creating a new table

Click the Add table button on the toolbar. The mouse pointer will change from an arrow to a cross.

Click the place on the working area where you would like the table to appear. You will be able to move it later if you wish.

The new table will appear, and the Edit table dialogue box will open.

Edit table dialogue box

You will see that the table has been given a placeholder name – new_table followed by a number. Replace this with the name you would like the table to have.

The ORDS system supports the use of spaces in table names, but this is not true of all database systems. If you are likely to want to export the data for use in other systems later on, it may be worth bearing this in mind. (See How to export a database for details of how to do this.)

It is also usually best to avoid punctuation or other special characters in table names, as these can sometimes cause problems. One exception to this is the underscore: these can be used safely in most systems, and are often used as a replacement for spaces.

Some people like to follow the convention of beginning every table name with the letters tbl – this can be done or not, according to personal preference.

You also have the option of adding a comment about the database table. This can be used to record metadata or notes about the table contents. Comments can also be added or edited later.

Click OK to close the dialogue box. You will see that your table has been automatically populated with a field called id.

Newly created database table

This will be the primary key field for the table – the field that provides a unique identifier for each record. The name of the primary key in an ORDS table is shown in bold type. You can change the field name if you wish – see section below 4.5 for details.

To view a comment on a table, hover the mouse pointer over the table header. (You can also click the header of the appropriate table to select it, and then click Edit table.)

Newly created database table showing comment

Comments on tables are also visible in the data editing interface (see section 2.5. of How to view and edit data). They are displayed towards the bottom of the screen, beneath the data table itself.

3.4. Adding new fields

Select the table you wish to add a field to by clicking its header. The border of the table will darken, and the rectangle representing the table in the thumbnail map will turn orange.

Now click the Add field button in the toolbar. (If no table is selected, the Add field button will be greyed out.)

The table will expand to show the options available when adding a new field.

Add new field options dialogue box

The field will have the placeholder name of new_field. Replace this with a name of your choice. As with table names, it is generally best to avoid special characters (other than underscores) in field names, and you may wish to avoid spaces if you are likely to want to export the data for use elsewhere.

The remaining fields in the dialogue box allow you to select a data type, and to set a number of other options. These are explained in sections 3.4.1., 3.4.2., and 3.5. below.

Once you are happy with your field name and choice of options, click Done. Your new field will be shown at the bottom of the table.

Database table with two fields

3.4.1. Selecting data types

The ORDS system offers a wide range of data types. These can be used to restrict the type of data that can be entered in certain fields in a database. For example, you may wish to specify that a date of birth field can only contain a date, or that a population field must contain a number. This can help to ensure consistency, which may be particularly useful if multiple people will be editing the same database.

To select a data type, use the Type drop-down list. To see a brief description of the data type, click Show help. Clicking this a second time will hide the description again.

A full list of data types, with more detailed descriptions, is given in section 3.5 below. These are all available for use in ORDS databases; however, for those who are relatively new to database design, ORDS offers a smaller subset of basic data types which are sufficient for the vast majority of use cases. For convenience, these are listed in their own section at the top of the Type drop-down list.

The ORDS basic data types are:

  • Integer: for whole numbers, negative or positive.
  • Decimal: for numbers (negative or positive) which may include a decimal point.
  • Text: for text, numbers, or a combination of these. This is the most flexible data type, so is often the best option if you are uncertain exactly what a field may need to contain.

Section 3.5 gives further information about these data types.

Some of the data types listed in section 3.5 are included chiefly for compatibility with other database systems: you may see these in imported databases, but there would rarely be a reason to create a new field in ORDS with this type.

It is worth noting that there are certain types of data which may at first sight appear to be numerical, but for which a field with the Text data type is actually more suitable. These include:

  • Phone numbers. These generally begin with a zero, which will be ignored if the field has a numeric data type. They may also include other non-numeric characters, such as spaces or brackets.
  • Number ranges – for example, page ranges in citations, or date ranges. These will include a dash or other non-numeric separator.
  • Information which is chiefly numeric, but which may sometimes need to be qualified. For example, a ‘Year’ field might normally contain a single four-digit number. Occasionally, however, the precise date might be uncertain, and you may wish to indicate this by putting ‘ca.’ before the number.

However, a wider range of search operators can be applied to numeric fields. For example, it is possible to write a query to find all the entries that are greater than or less than a specified value (so with a numeric 'Year' field, one might use the 'less than' operator to find all the records with a date before 1900). It is sometimes necessary to weigh the pros and cons of various data types - for further advice, please contact the ORDS help desk by emailing ords@it.ox.ac.uk.

3.4.1.1. Displaying data types

Data types in the ORDS are divided into four general categories. The colour of the field in the schema designer indicates which a field belongs to:

  • Numeric fields are coloured yellow
  • Character (that is, text) fields are coloured pink
  • Date and time fields are coloured green
  • Miscellaneous fields are coloured purple

See section 3.5. below for full details of the data types in each of these categories.

The schema designer can also be set to display the specific data type of each field.

Table showing data type labels

To turn on this feature, click the Options button on the toolbar, and then click the Show field data type in table design? checkbox so that a tick appears in it. Clicking this checkbox again will clear the tick and turn this feature off. Click OK to close the dialogue box. You will need to refresh your browser to see the data type labels. (Refreshing your browser will erase any changes made since the last save, so please ensure you save your database structure before refreshing - see section 5.1. below.)

3.4.2. Other ORDS field options

Size: Some ORDS data types also allow you to set the size of the field. This is done by entering a number (or for certain data types, two numbers separated by a comma) in the Size box, to specify a maximum number of characters. See the table in section 3.5 below for further details of how the size option works for different data types. If it is not possible to specify a size, this field will be greyed out.

The schema designer can be set to display the size of each field for which a size has been set. To turn on this feature, click the Options button on the toolbar, and then click the Show field size in table design? checkbox so that a tick appears in it. Clicking this checkbox again will clear the tick and turn this feature off. Click OK to close the dialogue box. You will need to refresh your browser to see the size labels. (Refreshing your browser will erase any changes made since the last save, so please ensure you save your database structure before refreshing.)

Table showing field sizes

The field size will be displayed in brackets. For some data types, if no field size has been specified, the size may be displayed as null. In this context, this simply means that there is no upper limit on the number of characters that can be entered in the field.

Default: This option allows you to specify what will appear in this field if no other data is added. When you first create a field, this will be set to NULL, here indicating that when a new record is added, the default is for that field to be empty. You can change this to something else if you wish (for example, you might wish the default entry to be Not known).

Autoincrement: This option is only available for integer fields. If this option is selected, then when a new record is added, the ORDS will automatically complete this field with the next available number.

When you create a new table, the id field is set by default to be an autoincrement integer field. This is the most straightforward way of ensuring each record has a unique primary key value.

Permit null values: In a database, a field with a null value is one that is currently empty. (This should be distinguished from 0, which indicates that the field has been completed with a value of zero.) If this checkbox is selected, it will be possible to leave this field empty. If it is deselected, the field will be mandatory – the ORDS will not allow a new record to be added unless the field has been completed.

Comment: You can add a comment to an individual field. This can be used to store notes about the field or the data in it – for example, you might use it to explain an abbreviation in the field name, or to indicate a unit of measurement. If you have deselected the Permit null values option as described above, it may be helpful to add a comment noting that the field is mandatory.

To add or edit a comment, click Edit comment, type your desired content in the dialogue box which appears, and then click OK. The comment will appear at the bottom of the field options dialogue box.

Add Field options dialogue box showing comment

To view a comment again later, hover the mouse pointer over the field. Alternatively, you can click the appropriate field to select it, and then click Edit field.

Comments on fields are also visible in the data editing interface: if a field has a comment attached to it, a small question mark symbol will display alongside the field name, and the comment can be viewed by hovering the mouse pointer over this. See section 2.5. of How to View and Edit Data.

3.5. ORDS data types – definitions

ORDS data types are divided into a number of categories. Colour coding is used to help distinguish these at a glance: yellow for numeric types, pink for character (text) types, green for date and time types, and purple for miscellaneous.

 

ORDS basic data types

Integer

A whole number, negative or positive.

This data type can be used for numbers between -2,147,483,648 and 2,147,483,647.

Decimal

A number (negative or positive) which may include a decimal point.

This field type can store numbers with up to 131072 digits before the decimal point, and up to 16383 digits after it, so is recommended for cases where a high degree of accuracy is required.

It is possible to specify a maximum total number of digits in the number (including those before and after the decimal point), and a maximum number of digits after the decimal point. This is done by entering two figures, separated by a comma, in the Size field in the ORDS schema designer.

In some cases, it may be desirable to restrict the number of digits after the decimal point, but not before. This effect can be achieved by using an arbitrarily large number – e.g. 100 or 1000 – before the comma, and the required number of decimal places after it. So, for example, for a field designed to display an amount of money given in pounds and pence, one might enter 1000,2 in the Size field. This would allow a total of up to a thousand digits, but only two of those after the decimal point.

Note that if only one number is entered in the size field, ORDS will interpret this as the number before the comma, and will assume that the number after the comma is a zero (so, for example, entering 100 as the size for a decimal field is equivalent to entering 100,0). In practice, this means that the field will only be able to store integers (and if the field already contains decimal data, this will be truncated). If you wish to be able to store decimal data, ensure either that the Size field is left completely blank, or that two numbers separated by a comma are entered. If the size field is left blank, ORDS will sometimes display the size as null, null - simply indicating that there are no specific restrictions on the number of digits that can be entered.

Text

Fields with this data type can contain text, numbers, or a combination of these.

This is the most flexible data type, so is often the best option if you are uncertain exactly what a field may need to contain. The length of Text fields is not restricted.

ORDS numeric data types (coloured yellow)

Integer

A whole number, negative or positive.

This data type can be used for numbers between -2,147,483,648 and 2,147,483,647.

Big integer

A whole number, negative or positive.

This data type can be used for numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. Use when the range permitted for the Integer data type is not sufficient.

Decimal

A number (negative or positive) which may include a decimal point.

This field type can store numbers with up to 131072 digits before the decimal point, and up to 16383 digits after it, so is recommended for cases where a high degree of accuracy is required.

It is possible to specify a maximum total number of digits in the number (including those before and after the decimal point), and a maximum number of digits after the decimal point. This is done by entering two figures, separated by a comma, in the Size field in the ORDS schema designer.

In some cases, it may be desirable to restrict the number of digits after the decimal point, but not before. This effect can be achieved by using an arbitrarily large number – e.g. 100 or 1000 – before the comma, and the required number of decimal places after it. So, for example, for a field designed to display an amount of money given in pounds and pence, one might enter 1000,2 in the Size field. This would allow a total of up to a thousand digits, but only two of those after the decimal point.

Note that if only one number is entered in the size field, ORDS will interpret this as the number before the comma, and will assume that the number after the comma is a zero (so, for example, entering 100 as the size for a decimal field is equivalent to entering 100,0). In practice, this means that the field will only be able to store integers (and if the field already contains decimal data, this will be truncated). If you wish to be able to store decimal data, ensure either that the Size field is left completely blank, or that two numbers separated by a comma are entered. If the size field is left blank, ORDS will sometimes display the size as null, null - simply indicating that there are no specific restrictions on the number of digits that can be entered.

Single precision

A number (negative or positive) which may include a decimal point.

Although this type of field (known as a floating point type) can be used to store very large or very small numbers, the precision (i.e. the number of significant figures, before or after the decimal point) is limited to 6 digits. In practice, this means that rounding of large or small numbers may occur.

This data type is included chiefly for compatibility with other database systems. When creating a new field, it will generally be preferable to use the data type Decimal.

Double precision

A number (negative or positive) which may include a decimal point.

Although this type of field (known as a floating point type) can be used to store very large or very small numbers, the precision (i.e. the number of significant figures, before or after the decimal point) is limited to 15 digits. In practice, this means that rounding of large or small numbers may occur.

This data type is included chiefly for compatibility with other database systems. When creating a new field, it will generally be preferable to use the data type Decimal.

ORDS character (text) data types (coloured pink)

Char

Also known as Character, fields of this type can contain text, numbers, or a combination of these.

Char fields have a fixed length, set by specifying a number of characters in the Size field in the ORDS schema designer. If the entry in a Char field is longer than the specified number of characters, it will cause an error; it if is shorter, the field will be padded out with spaces. If no field length is specified, the default is for the field to be limited to a single character.

This data type is included chiefly for compatibility with other database systems. In some other systems, using the Char field type confers performance advantages. However, this is not true for PostgreSQL systems such as the ORDS (in fact, the padding will increase the file size), and hence for ORDS databases it will generally be better to use the Varchar or Text field types.

Varchar

Also known as Character varying, fields of this type can contain text, numbers, or a combination of these.

It is possible (though not required) to set a maximum length for a Varchar field, by specifying a number of characters in the Size field in the ORDS schema designer. Entries which are longer than the specified number of characters will be truncated.

In PostgreSQL database systems such as the ORDS, there is no practical difference between a Varchar field with no length limit and a Text field. In some other systems, however, these are handled differently, and hence both options are included to increase compatibility with other systems.

Text

Fields with this data type can contain text, numbers, or a combination of these.

This is the most flexible data type, so is often the best option if you are uncertain exactly what a field may need to contain. The length of Text fields is not restricted.

ORDS date and time data types (coloured green)

Date

A date consisting of a day, month, and year.

Dates should be entered in the YYYY-MM-DD format.

If you wish to display dates in other formats, use the Text data type. (In some circumstances, it may be worth considering having separate day, month, and year fields, to aid sorting and searching.)

Time

A time of day, consisting of hours, minutes, and seconds. Times should be entered in the format hh:mm:ss.

If you wish to display times in other formats, use the Text data type.

Timestamp

A concatenation of a date and a time.

Timestamps should be entered in the format YYYY-MM-DD hh:mm:ss.

ORDS miscellaneous data types (coloured purple)

Boolean

Data entered in fields of this type is restricted to one of a pair of values. Depending on the context, the pair may be interpreted as true or false, yes or no, or 0 or 1.

In an ORDS database table, a field with the Boolean data type will be displayed as a check box.

Binary data

Used for raw binary data (that is, data consisting of 0s and 1s), stored as hexadecimal strings.

 

3.6. Adding relationships between tables

3.6.1. Types of relationship

Relationships are a way of linking database tables. Two types of relationships are commonly used: one-to-many, and many-to-many.

One-to-many relationships exist between a single entity of one kind, and multiple entities of another. For example, a child can only have one biological mother, but a mother may have many children. (Some mothers, of course, may only have one child, but this does not change the nature of the relationship – the important point is that it is possible for a mother to have multiple children.)

Many-to-many relationships exist between multiple entities of one kind and multiple entities of another. For example, a brother may have multiple sisters, and a sister may have multiple brothers. (Again, the fact that some brothers may have only one sister – or none at all – does not affect the nature of the relationship.)

It is also possible to have a one-to-one relationship within a database. In practice, however, these are rarely used. Within the ORDS system, a one-to-one relationship would be implemented in the same way as a one-to-many relationship, but no entity would be linked to more than one entity of the other type.

3.6.2. Adding one-to-many relationships in the ORDS

Suppose that a database includes a table which has a number of fields for details of books in a collection. (The table shown below has the Show field data type in table design? option turned on – see section 3.4.1.1. above for details.)

Database table - tblbooks

Consider the Publisher field. Each book will have a single publisher, but each publisher is likely to have published many books. This is therefore a one-to-many relationship.

The first step is to create a second table, with fields for the information we would like to record about the publishers.

Database table - tblpublishers

We now need to join the two together. It is best to think of a one-to-many relationship as linking the entries in one field in one table (in this case, the Publisher field in tblbooks) with whole records from another table (in this case, tblpublishers).

We will therefore create the link between the Publisher field in tblbooks and the primary key field in tblpublishers. The primary key is the field that the ORDS uses to uniquely identify the records in a table (it may be helpful to think of the contents of the primary key field as representing the record as a whole). In the schema designer, ORDS shows primary keys in bold - in this table, the id field.

A field that is linked to the primary key of another table is known as a foreign key. So in this case, the foreign key is the Publisher field in tblbooks.

When creating the relationship, it will be helpful to have the two tables side by side in the schema designer. To move a table, click the header, and drag to the desired location.

tblbooks and tblpublishers

The next step is to ensure that the two fields to be linked have the same data type. This means we need to change the data type of the Publisher field to Integer. (If this seems counter-intuitive, remember that the tblpublishers id field represents the whole record: it is an Integer field representing a number of fields of various types.)

To do this, click the Publisher field to select it (two small red arrows will appear at the left-hand end of the field), and then click the Edit field button in the toolbar. The field will expand to show the available options: use the Type drop-down list to change the data type to Integer, then click Done.

(Note: in this example, tblbooks is a newly created table containing no records, so it is straightforward to change the data type. If the table already contained records, it would be necessary first to ensure a copy of the original data had been saved, so it could be used to populate tblpublishers, and then to modify the contents of the Publisher field to make it compatible with the new data type. For advice on transforming data in this manner, please contact the ORDS help desk by emailing ords@it.ox.ac.uk.)

tblbooks and tblpublishers - after data type change

Now click the id field of tblpublishers to select it. This will activate the Connect foreign key button on the toolbar; click this button.

To create the relationship, click the Publisher field in tblbooks. A curved line will appear, joining the two fields.

tblbooks and tblpublishers with relationship

This is a good point to save your changes (see section 5.1. below).

If you exit the schema designer and go to the editing page or the Add new record form for tblbooks (see the later guide in this series, How to view and edit data, for more details), you will see that the Publisher field now appears as a drop-down list, which allows you to select any of the records from tblpublishers.

The screenshot below shows the drop-down list on the editing page, after one record has been added to tblbooks. (Three records have also been added to tblpublishers to populate the drop-down list.)

tblbooks in editing view, showing drop-down list of records from tblpublishers

3.6.3. An alternative method: creating new foreign keys

The example above linked a primary key to an existing foreign key field. However, it is also possible to use ORDS to create the foreign key itself, as part of the process of creating the relationship.

Suppose that when creating tblbooks, the Publisher field had been omitted. The table would therefore look like this:

tblbooks without Publisher field

To create a new foreign key field, click on the primary key field of the linked-to table (in this case, id in tblpublishers). Two small red arrows will appear at the left-hand end of the field, to show it has been selected.

tblbooks without Publisher field alongside tblpublishers

Click the Create foreign key button in the toolbar, then click the header of the table in which you want the foreign key to appear (in this case, tblbooks). A new field will appear at the bottom of this table, linked to the tblpublishers id field by a curved line.

tblbooks and tblpublishers tables, with relationship

Unfortunately, it's not possible to change the order of fields in an ORDS table after they have been created (we hope that it may be possible to offer this feature at some point in the future). If it is important to have the fields in a specific order, they should therefore be created in that order - in this case, that would involve creating tblpublishers and the first few fields tblbooks, then adding the relationship between the tables which creates the foreign key field in tblbooks, then creating the remaining fields in tblbooks.

3.6.3.1 Names of foreign key fields

When a foreign key field is created this way, the default pattern for its name is the name of the primary key (in this case, id), followed by an underscore, and finally the name of the table containing the primary key (in this case tblpublishers).

id_tblpublishers field

If desired, the field name can be edited  – see section 4.5. below. However, one advantage of creating foreign keys using this method is that it offers a straightforward way of ensuring foreign key fields follow a consistent naming pattern. This also makes foreign key fields easier to recognize.

It is also possible to change the way that foreign key field names are generated. To edit the pattern that is used, click the Options button on the schema designer toolbar. The Pattern for foreign key names field displays the current pattern.

Schema designer options dialogue with Pattern for foreign key names field highlighted

In this field, the ORDS uses three codes to represent various database elements. By combining these (with, if desired, other characters), you can control the form that foreign key names will take.

  • %T is used to represent the name of the primary table - that is, the table you first click on when creating a foreign key field as described above
  • %R is used to represent the name of the primary key field in the primary table
  • %t is used to represent the name of the foreign table - that is, the table in which the foreign key will be created

In the example used above, the primary table is tblpublishers, the primary key field in the primary table is id, and the foreign table is tblbooks.

Two database tables - labelled

The default pattern for foreign key names is %R_%T, or the name of the primary key field, followed by an underscore and the name of the primary table. With these two tables, this generates the foreign key name id_tblpublishers.

The pattern can be edited to generate other types of foreign key name. For example, in this case:

  • %R_from_%T would produce the foreign key name id_from_tblpublishers
  • %T_in_%t would produce the foreign key name tblpublishers_in_tblbooks
  • %R_%T_in_%t would produce the foreign key name id_tblpublishers_in_tblbooks

A prefix or suffix can also be included in the pattern. For example, one might add the prefix FK to indicate that that field is a foreign key.

  • FK_%R_%T would produce the foreign key name FK_id_tblpublishers

Spaces should be avoided in foreign key name patterns, as these can sometimes cause problems. Underscores can safely be used to replace spaces, but other than these (and the percentage signs which form part of the ORDS codes), special characters should also be avoided in foreign key name patterns.

Once you are happy with your changes to the pattern, click OK to close the Options dialogue box.

The edited pattern will apply to all new foreign keys created using the Create foreign key button. The pattern is stored using a browser cookie, meaning that (assuming you have cookies enabled) the changes will apply to all databases in all projects you access through that web browser. If you access the ORDS via a different device (or via a different browser on the same device), the changes will not apply - but you can, of course, also edit the foreign key pattern on the new device or browser.

3.6.4. Adding many-to-many relationships

As in most database management systems, many-to-many relationships are handled in ORDS using link tables.

Suppose that we wanted to add a relationship between tblbooks and another table listing author details. One option would be link the new authors table to the Author field in tblbooks using the process described in section 3.6.2. above. This would create a one-to-many relationship, meaning it would be possible to list the same person as the author of multiple books. However, some books have multiple authors, and this data structure would give us no way to express that.

It might seem tempting to try to address this by creating additional author fields in tblbooks. However, this is a risky strategy: it involves guessing the maximum number of authors a book is likely to have.

A better way to solve the problem is to create an additional link table which lists all the various author-book pairs. For example, if A Natural History of Great Britain was written by Smith, Jones, and Brown, three records would be added to this table, one for each author-book combination.

First, let’s create the table for the author information.

Two database tables - tblauthors and tblbooks

(For convenience, the new table has been placed to the left of tblbooks. The relative placement of tables in the schema designer working area does not change the database structure; tables can be moved around as desired.)

Now let's add the link table to store author-book combinations. It will be easiest to add the necessary relationships if the new table is positioned between tblauthors and tblbooks. We'll call it tblbookauthors.

Three database tables - tblauthors, tblbookauthors, and tblbooks

We now need to add two further fields to the new table: one which will be joined to the id field of tblauthors, and one which will be joined to the id field of tblbooks.

The quickest way to do this is to use the method of creating foreign keys described above.

To create a foreign key for the tblauthors id field, click tblauthors id, then Create foreign key, then the header of tblbookauthors. The foreign key field will appear in tblbookauthors, with a link to the tblauthors id field.

Three database tables - tblbookauthors with foreign key field

Repeat this process for the tblbooks id field.

Three database tables - tblbookauthors with two foreign key fields

This is a good point to save your changes (see section 5.1. below).

Link tables will often consist only of their own primary key field, plus two foreign key fields. However, it can sometimes be useful to add additional fields. For example, we might add a Role field to tblbookauthors, which could be used to indicate the part someone had played in the writing of a book (main author, co-author, editor, translator, etc.).

Three database tables - tblbookauthors has Role field

tblbookauthors would be the most appropriate place for this field, as being (for example) the main author is a property of a particular act of authoring, rather than of an author – because an author might be the main author of one book, and the co-author of another.

Information about the authors of various books will be retrieved by running a database query. The Author field in tblbooks is now superfluous, so needs to be deleted to avoid confusion. As this a new (and therefore empty) database, this can be done immediately. However, if you are working with an existing database which already contains records, it is important to first make sure you have a copy of all the relevant information, so it can be used to populate the new tables. The easiest way to achieve this is to run a database query which gives you the full contents of the pre-existing table (tblbooks in this example), and export the query results as a CSV file - see How to query data for details of how to do this.

At present, the population of the new tables would need to be done manually. However, if you have a lot of data that needs to be transformed in this way, please contact the ORDS help desk by emailing ords@it.ox.ac.uk, as in some cases it may be possible for the ORDS team to write a script to partially automate the process.

Once a copy of all the relevant information has been saved, click the Author field to select it, then click the Remove field button on the toolbar. You will be asked to confirm that you really do wish to delete the field. To continue, click OK.

4. Editing the structure of an existing ORDS database

4.1. Viewing the database structure

To view the structure of an existing database in the ORDS system, go to the database page. (From the ORDS welcome page, this can be reached by clicking the name of the project which contains the database, and then clicking the name of the database.)

The database page displays your database’s details:

Database page after database upload

This page also allows you to edit database details, and to create up to three versions of each database. For further details see, How to manage databases and database versions.

To edit the structure of a database version, click the View and edit database structure button. You will be taken to the ORDS schema designer.

4.2. The ORDS schema designer page

The ORDS schema designer is a graphical interface that allows you to edit the database schema, or structure. The schema consists of tables, fields within those tables, and relationships between fields.

Schema design page showing tables

The page consists of three main components.

The working area occupies most of the screen. The database tables are displayed here, with the relationships between them represented by curved lines.

The toolbar is in the top right-hand corner. This can be hidden and unhidden by clicking the small arrow at the top.

The thumbnail map is in the bottom right-hand corner. This gives an overview of the whole working area – small versions of each table appear here. The part of the working area currently displayed on the screen is shown outlined in red.

4.2.1. Arranging tables in the working area

Tables can be moved around the working area by clicking on the table header and dragging.

Clicking the Align tables button on the toolbar will arrange the tables side-by-side (and then, if there are enough tables, in rows),  starting in the top left-hand corner of the working area.

4.3. Editing table names and comments

Select the table you wish to edit by clicking its header. The border of the table will darken, and the rectangle representing the table in the thumbnail map will turn orange. Now either double-click the header, or click the Edit table button on the toolbar to open the Edit table dialogue box.

Edit table dialogue box

Edit the table name and/or comment as desired, then click OK to close the dialogue box.

4.4. Deleting tables

Select the table you wish to delete by clicking its header. The border of the table will darken, and the rectangle representing the table in the thumbnail map will turn orange.

Click the Remove table button on the toolbar. You will be asked to confirm that you really do wish to delete the table and all its contents. Please note that once you have saved your changes to a schema, deleting a table cannot be undone. To continue, click OK.

4.4.1. Deleting all tables in a database schema

To delete all the tables in a database schema, click the Clear tables button on the toolbar.

You will be asked to confirm that you really do wish to delete all tables and their contents. Please note that once you have saved your changes to a schema, deleting tables cannot be undone. To continue, click OK.

4.5. Editing field names and options

Select the field you wish to edit by clicking it. Two small red arrows will appear at the left-hand end of the field.

tblpublishers - field selected

Now either double-click the field, or click the Edit field button on the toolbar. The field will expand to show the available options.

tblpublishers - field expanded

Edit the field name and options as desired.

The data type can be changed by selecting an option from the drop-down list. To see a brief description of the currently selected data type, click Show help. Clicking this a second time will hide the description again. More information about selecting data types is available in section 3.4.1. above. See section 3.5. for a detailed list of all available data types.

For more information about other field options, see section 3.4.2. above.

To shrink the field and hide the options, click Done.

4.6. Deleting fields

Select the field you wish to delete by clicking it. Click the Remove field  button on the toolbar.

You will be asked to confirm that you really do wish to delete the field and all its contents. Please note that once you have saved your changes to a schema, deleting a field cannot be undone. To continue, click OK.

4.7. Editing relationships between tables

To change the nature of a relationship between tables, delete the original relationship and add a new one.

To delete a relationship, click the foreign key field to select it, and then click the Remove foreign key button on the toolbar. This will not actually remove the foreign key field itself; it will simply stop it from being a foreign key by deleting the relationship.

Occasionally, a problem can arise if an attempt is made to delete a relationship shortly after it is created (because, for example, it was accidentally created in the wrong place): in some circumstances, the ORDS fails to recognize that the relationship exists, and hence is unable to delete it. This is a known issue, and will be resolved as soon as is feasible. In the meantime, this problem can usually be solved by saving changes to the database structure, exiting the schema designer, and then re-entering. The ORDS should then recognize the relationship, and it can be deleted as normal. If this workround is unsuccessful, please contact the ORDS help desk by emailing ords@it.ox.ac.uk.

For details of how to add new relationships between tables, see section 3.6. above.

4.8. Editing primary and other keys

As noted in section 3.3. above, tables created using the ORDS schema designer will automatically be given a primary key field. If a CSV file is uploaded to the ORDS, a primary key field will be added to the resulting table (existing primary key fields in uploaded Access or SQL databases will be retained). By default, primary key fields created by the ORDS will be autoincrement Integer fields.

In some circumstances, however, you may wish to edit the primary key of a table. For example, you may wish to use an existing field from a CSV file as the primary key for that table, rather than the ORDS-created one. Before doing this, you should ensure that the new field is suitable to be used as a primary key - in particular, that it does not contain any duplicate or null values.

4.8.1. Types of key in the ORDS

Although primary key fields are probably the most familiar and widely used, the ORDS actually supports three separate types of key field.

  • Primary keys serve as the identifier for each record in a table. A primary key field never permits null values (that is, it cannot be left blank - every record in the table must have a primary key), and must be unique within the table (that is, no two records within the same table can have the same primary key).
    For this reason, autoincrement Integer fields are a good choice for primary keys, as the ORDS will automatically add the next available number to the field when a new record is created. However, as long as the field is completed with a unique value for each record, primary key fields can also have other data types.
  • Unique keys are fields whose content must be unique within the table. However, unlike primary keys, unique key fields may permit null values.
    A unique key field might be used, for example, for catalogue numbers for a series of artefacts. If catalogue numbers are assigned gradually, it may be that not all items have a catalogue number. However,  it may be important to ensure that where a catalogue number has been assigned, it is not shared by any other artefact listed in the same table. In this case, making the catalogue number field a unique key would allow it to be left blank where a number has not yet been assigned, but ensure that the each number assigned was unique.
  • Index keys are used to create an index on the specified field. In some cases, this can enhance database performance (however, it needs to be done with care: inappropriate index keys can have the opposite effect, and slow down a database). Index key fields can permit null values, and their contents do not need to be unique.
    Adding index keys should not generally be necessary in the ORDS: if you are experiencing database performance problems, please contact the ORDS help desk by emailing ords@it.ox.ac.uk, to eliminate other possible causes.

4.8.2 Changing the primary key field of a table

To change the primary key field of a table, click the table header in the schema designer to select the table. Then click the Keys button on the toolbar. This will display the Keys dialogue box.

Keys dialogue box

To remove the existing primary key, click the Remove key button. (In the rare situation where the table already has multiple keys, you will first need to select the appropriate one from the Keys in table drop-down list.)

To add a new key, click the Add key button. (By default, this will be a primary key: to add a unique or index key, first select the desired option from the Type: drop-down list in the Edit key pane.)

Now type a name for the key into the Name: field in the Edit key pane. (If this step is omitted, the process of trying to create a new primary key will fail. The name of the key can be anything you wish, though it is best to stick to letters, numbers, and underscores. Primary keys created by ORDS will have a name beginning pkey_ - you can follow this convention or not, as desired.)

Click the name of the field you wish to use as the new primary key in the Available fields list, then click the left-pointing arrow button to move the field name into the Fields in key list. (You can create a multi-field primary key by moving multiple field names into the Fields in key list.)

The Keys dialogue box should now show the new primary key name in both the Keys in table drop-down list and the Name: field, and the name of the field(s) being used as the key in the Fields in key list.

Keys dialogue box with updated information

To complete the process and create the new primary key, click OK.

5. Saving and exiting

5.1. Saving the database structure

The schema designer does not automatically save changes. It is therefore important to ensure you save the schema before leaving the page, or any edits you have made will be lost.

To save your schema, click the Save button on the toolbar.

You will be asked to confirm that you wish to replace the current database structure with the new version. To continue, click OK.

The working area will turn grey, and you will will see a whirling symbol towards the top of the screen as ORDS attempts to save the database.

If the save operation is successful, you will see a message confirming that your changes have been saved. Click OK to close the dialogue box.

5.1.1. Problems with your schema

If there is a problem with your schema design, the save operation will fail, and you will see an error message. This may happen if you have edited the schema in a way that would result in data loss – changing a field that contains text to the Integer data type, for example.

You will need to find and fix the problem before you will be able to save your changes. If you have made a lot of changes between saves, locating the problem can be a time consuming and frustrating task: it is therefore a good idea to get into the habit of saving your work frequently.

If you cannot resolve the problems with your database structure, or think you have encountered a system error which is preventing you from saving changes, please contact the ORDS help desk by emailing ords@it.ox.ac.uk.

5.1.2. Reverting changes

If you make changes to the database structure but then decide you do not want to save them, you can return the database structure to the state it was in immediately after it was last saved by clicking the Revert changes button on the toolbar. You will be asked to confirm that you wish to do this: click OK to continue.

5.1.3. System timeout

If you leave the schema designer open for an extended period without working on it, there is a risk that the system will time out, and you will find you are unable to save any changes previously made. It is therefore sensible to save changes whenever you step away from your computer, or if you change windows to work on another task.

5.2. Exiting the schema designer

To exit the schema designer, click the Exit button on the toolbar.

If you have made any changes since the database structure was last saved, ORDS will alert you to this, and ask if you are sure you want to exit. To return to the schema designer to save your changes, click Cancel. To continue to the database page without saving your changes, click OK.

6. What next?

You may want to have a look at some of the later guides in this series. For a full list, visit the ORDS documentation home page.

You can also find out more about the ORDS service by visiting the ORDS home page, http://ords.ox.ac.uk/. If you have specific queries, you can contact the ORDS help desk by emailing ords@it.ox.ac.uk.

Service area: 

Written by IT Services. Latest revision 8 August 2016