Creating a relational database

You'll learn how to model and create a relational database schema on SlicingDice using our Control Panel.

When to use relational databases

SlicingDice is built to be schemaless, so every data that you have is stored only in a single table (called dimension in SlicingDice) based on a denormalized (non-relational) model. But, in some situations, you may think about using a relational model.

When some attributes of your data are updated constantly, you may consider using a relational model. An example is when a product is related to a client code. If you need to update for example the product color, in denormalized models you'll need to update several rows, but in a relational model, you can just update the product in its table.

You also need to think about using relational modelling when your dataset is complex, like Entities having their own varied attributes. As an example, let's think about a movie. A movie has its own attributes, like the director. But the director itself may have a whole range of attributes. Makes more sense to have information about a director in a separate dimension.

Types of dimensions

Take a look at the following relational database model based on a Star Schema, that will be used on this tutorial.

Star Schema model

The image above shows four dimensions: Dim_Date, Dim_Store, Dim_Product and Fact_Sales. The three first dimensions have a connection with Fact_Sales. Using the above example, we can see that we have two types of dimensions.

  • Dimension Table: The dimension where you'll normally store data not related to time (non-time-series), and this data will be referenced by the Fact dimension.
  • Fact Table: The dimension where you normally store events/transactions that have a lot of repetition and, because of that, will reference the other dimensions through a Foreign Key.

In this particular case, every sale will have a date and time (timestamp), a product and a store that executed the sale. Every date, product, and store has its own row in their own dimensions. When a sale transaction is registered, all the data of that sale will be referenced and related in the Fact_Sales dimension.

Creating a database

To be able to model a new relational database you'll need to create a physical database first. On Control Panel access the physical databases section, click on Create New Database and a 3-step wizard will be shown. The Step 1 is where you'll need to provide basic database information, like name, type and pricing model.

Screenshot of Step 1 of Creating a database

Field Mandatory Description
Database Name Yes The name of your database. Can be edited at any time.
Database Labels/Tabs No Possible labels/tags you might want to associate to a database, in order to organize it. Can be edited at any time.
Database Description No The description of your database. Can be edited at any time.
Cloud Provider/Region No The cloud provider and its supported region that will be used to store and query your data. Cannot be edited after creation.
Database Type Yes Can either be Test or Production. It's only possible to change a Test database to Production, not the other way around.
Pricing Model Yes The pricing model you want to use for your database. Can be changed at any time.
Insertion Load Yes The insertion throughput you want your database to have. Can be changed at any time.
Query Load Yes The query throughput you want your database to have. Can be changed at any time.

These parameters in the first step are the basic ones that you need to create a default database.
We're setting this database name to be SalesDB and to be a Test database.

On the next tutorial step we'll start modeling our database to be relational, creating all the dimensions we need.

Creating a dimension

You'll now need to create the Dim_Date, Dim_Store, Dim_Product and Fact_Sales dimension. Go to Physical databases and click on the Edit icon on the database you have just created. Then go to Schema Definition. Click on Create New Dimension. You'll need to insert the following information for each of the dimensions you're going to create:

  • Dimension Name: The dimension name that will be shown on control panel.
  • Dimension API Name: The dimension API name.
  • Relational Model Type: Defines if you want to set the relational model type on your dimensions (it's the case for this tutorial). If the checkbox is selected, you can select one of these options: Fact Table (supports Foreign Key) or Dimension Table (doesn't support Foreign Key).
Schema definition step
    that allows you to create dimensions

Remember that for the Fact_Sales dimension you need so set the relational model type as Fact Table and for the other dimensions you should set this parameter as Dimension Table.

Name Relational model type
Fact-Sales Fact Table
Dim-Date Dimension Table
Dim-Store Dimension Table
Dim-Product Dimension Table

After all items are set, click on Save Dimension. In order to follow this tutorial, we expect you to create the following dimensions.

Creating a column

Now, you'll need to relate the Fact table with the Dimension tables you have created through a definition of Foreign Keys that should be created as columns for the Fact table. For this example you'll need to create three Foreign Key columns and reference each one with each dimension table you have. In the Schema Definition, click on Create New Column. You'll need to insert the following information:

  • Name: Column name that will be used and shown in Control Panel.
  • API Name: Column API name.
  • Description (optional): Description of this column, used to add more information about it.
  • Dimension: Dimension that will contain this column (in this case Fact_Sales).
  • Type: Data type supported by this column (in this case Foreign Key).
  • Data Storage Type: Select if your column will store only the latest inserted value or all inserted values.
  • Referenced Dimension (only for Foreign Key Data Type): Select which dimension will be referenced by the Foreign Key (select the Dimension Table that will be related to the Fact through this column).
Schema definition step
    that allows you to create columns

With everything defined, you can click on Save Column. Notice that your column will be successfully created and it will be shown on the schema tree for the database and dimension chosen. So now, you'll be able to use this database as a relational one.