How to use the schema tool: a step-by-step guide

At LinkORB, we use the schema tool to document databases that our applications rely on. This guide shows how to collaborate with your team members to document databases like a pro 👌.

Let’s explore the different components of the schema tool by creating and editing a schema for a fake e-commerce site called MemeFactory that sells a wide range of ‘meme-printed’ products such as tee-shirts, coffee mugs, calendars, magnets, wall art, stationery, and greeting cards. Customers can browse and purchase their favourite meme-inspired items, adding a touch of humour and sarcasm to their lives. 😜

The database associated with this web application is named e-commerce (since MemeFactory is an e-commerce site).

Add a new schema

Let’s create a schema for the e-commerce database.

Add a schema from the Schemas page and fill in the details using the sample data below.

Field	Enter / Select this value	Optional / Mandatory
Name	e-commerce_[YOUR INITIALS]	Mandatory
Read role	ROLE_USERGROUP:SCHEMATA-TEAM	Mandatory
Description	Documents MemeFactory's database	Optional

Add a table to the schema

Right now, our schema looks mighty empty 🤔. Ah! That is because we have not added any tables or columns. Let’s add a new Orders table to the schema!

Add a new table from the new e-commerce schema page and fill in the details using the sample data below.

Field	Enter / select this value	Optional / Mandatory
Name	Orders	Mandatory
Doc Status	DRAFT	Optional
Description	Stores customer orders	Optional

Add a column to the table

Our new Orders table needs some columns. Let’s add a new order_number column that specifies how much meme merch was sold per order.

Add a new column from the new Orders table page and fill in the details using the sample data below.

Field	Enter / Select this value	Optional / Mandatory
Name	order_number	Mandatory
Type	string	Mandatory
Nullable	yes	Mandatory
Length	string(128)	Mandatory
Doc status	REVIEW	Mandatory

Edit columns

Try to figure out how to edit the order_number column and update the length from string(128) to string(32).

Import a schema

You may have noticed that adding tables and columns can be a bit laborious 😫. But don’t worry! Fortunately, you also have the option to import a schema in either .csv or .xml format.

XML schemas define tables and columns using XML tags, as the following template shows:

<table>
  <column/>
  <column/>
  <column/>
</table>

Let’s import a completed e-commerce schema that includes all the columns and tables we need to complete the rest of the steps.

1. Create an XML document in the text editor of your choice called e-commerce.xml and paste the following code into it:

<?xml version="1.0"?>
<schema>
  <table name="Customers" docStatus="INPUT" label="" primaryKeyColumn="customer_id">
    <column name="customer_id" type="integer" docStatus="DRAFT" notnull="false"/>
    <column name="name" type="string" docStatus="DRAFT" notnull="false"/>
    <column name="email" type="string" docStatus="DRAFT" notnull="false"/>
    <column name="address" type="string" docStatus="DRAFT" notnull="false"/>
  </table>
  <table name="Products" docStatus="DRAFT" label="" primaryKeyColumn="product_id">
    <column name="product_id" type="integer" comment="PK of the products table." docStatus="REVIEW" notnull="false"/>
    <column name="order_item_id" type="integer" docStatus="DRAFT" notnull="false"/>
    <column name="order_id" type="integer" docStatus="DRAFT" notnull="false"/>
    <column name="quantity" type="integer" docStatus="DRAFT" notnull="false"/>
    <column name="price" type="integer" docStatus="DRAFT" notnull="false"/>
  </table>
  <table name="Orders" docStatus="REVIEW" label="" primaryKeyColumn="order_id">
    <column name="order_id" type="integer" comment="uniquely identifies each row." docStatus="REVIEW" notnull="false"/>
    <column name="order_date" type="integer" comment="date when order is placed." docStatus="DRAFT" notnull="false"/>
    <column name="customer_id" type="integer" docStatus="DRAFT" notnull="false" relation="Customers.customer_id"/>
    <column name="order_number" type="string" length="32" comment="Provide feedback on this newly added column." docStatus="REVIEW" notnull="false"/>
  </table>
  <table name="Payments" docStatus="ACTIVE" label="" primaryKeyColumn="payment_id">
    <column name="payment_id" type="integer" docStatus="DRAFT" notnull="false"/>
    <column name="order_id" type="integer" docStatus="DRAFT" notnull="false"/>
    <column name="payment_method" type="string" docStatus="DRAFT" notnull="false"/>
    <column name="amount" type="integer" docStatus="DRAFT" notnull="false"/>
  </table>
</schema>

2. Save the file.

This code is our fully fleshed-out schema that contains all the tables and columns we will work with going forward.

3. From the e-commerce schema page, import the XML schema. You may leave the Upload from Beginning/scratch? ticked.

And voilà! 🥳

You now know how to import schema tables and columns in bulk like an efficient developer 🤓.

Give the table a primary key

If you view the Orders table, you’ll notice we have a few more columns after we imported the XML schema. But something was left out of the XML schema file — our table needs a primary key to identify each order.

Edit the Orders table and select order_id from the Primary Key Column drop-down.

If you look at the table columns again, you’ll notice an asterisk * next to the order_id column to indicate it is the primary key for the Orders table.

Now each order placed by customers has an order_id.

Add foreign keys

A foreign key is a column or group of columns in a relational database table that links data in two tables. You can add foreign keys to a table using the schema tool.

1. From the Orders table page, open customer_id.

The Relational table and Relational column fields specify foreign keys:

• The Relational table field refers to the table that contains the column you wish to use as the foreign key.
• The Relational column field refers to the column belonging to the relational table you wish to use as the foreign key.

2. For Relation table, select Customers.
3. For Relation column, select customer_id.

The Orders table now uses the customer_id as its foreign key 💫.

Tag use cases

Tags are optional but can be helpful. They are usually applied to large databases like onatal, orfeus, and mosos, to name a few. Don’t use tags for small databases like upr. However, it’s okay to tag plaza PII and subsystems.

Let’s look at a few use cases for tags:

• Add tags to PII fields to generate input for compliance docs (LinkORB must report which fields it processes and why).

• Add tags to fields to be anonymized, stripped, or shuffled before they are sent to analytics databases.

• Add tags to application subsystems/components to quickly understand a limited set of tables and columns.

Best practices when writing descriptions

• Use short descriptions for schemas, tables, and columns. Descriptions do not need to be complete sentences.

• Start each sentence with a capitalised word and end each sentence with a period since descriptions may require multiple sentences.

• There is no need to acknowledge that you are describing a schema, table, or column or that it contains something (since that is obvious). So, remove phrases/words like:

  • this schema/table/column
  • contains
  • stores
  • holds

  For example, instead of saying:

  file_version - Table that contains file and image metadata.

  Focus on what data it stores:

  file_version - File and image metadata.

• Provide additional information in a table’s description only if such information is relevant to the whole table. Move all column-specific information to the Comment field of the affected column(s).

• Avoid using a column’s Comment section to describe relationships. Instead, document relationships using the Relation fields.

  For example, if the id column of the customer table has a one-to-one (foreign key) relationship with the customer_id column of the purchases table, do not document that relationship in the customer table’s description like the following example does:

  customers - Customer details. customer.id uses purchases.customer_id as its foreign key.

  Instead, remove the second sentence:

  customer - Customer details.

  Then, set the customer.id column’s Relation table field to purchases and the Relation column field to customer_id.

• To write a meaningful description, consider the user’s possible questions. For example, for an image_size column, is the size in bits, bytes, or decimals? Does it represent the original file size or the compressed file size?

Sample review workflow

When working together to document schemas, use the following workflow as guidance:

1. Contact the SME to review INPUT items.

2. The SME updates the Doc Status to DRAFT after they have added their input.

3. Review the DRAFT items and make updates where necessary.

4. Change the Doc Status of updated items to REVIEW.

5. Consult the project lead for feedback once a schema has been documented thoroughly and is in REVIEW status.

6. Change the Doc Status to DRAFT if any items need more work.

Rinse and repeat.

Remember to follow LinkORB’s guidelines for getting help and how to conduct effective asynchronous communication while documenting schemas.