star-schema

Posted on | In , |

Table Design

https://github.com/yoosuf/Messenger

Constraints

Key words

  • NOT NULL
    • Ensures that a column cannot have a NULL value
  • UNIQUE
    • Ensures that all values in a column are different

  • PRIMARY KEY

    • A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
    • A table can have only one primary key, which may consist of single or multiple fields

  • FOREIGN KEY

    • Uniquely identifies a row/record in another table
    • A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
    • The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
  • CHECK
    • Ensures that all values in a column satisfies a specific condition
  • DEFAULT
    • Sets a default value for a column when no value is specified
  • INDEX
    • Used to create and retrieve data from the database very quickly

Examples

1
2
3
4
5
6
CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

Star Schema

  • Denormalized dimensions
  • Fact table surrounded by dimension tables
  • may have data integrity and storage issues

Star design vs. snowflake design

Star

  • denormalized
  • redundancy (more storage space)
  • simple joins
  • faster performance
  • may run into data integrity issues
  • safe to use when facts constitute 80% or more

Snowflake

  • normalized
  • not redundant (low storage space)
  • complex and multiple joins
  • can be relatively slow because of more joins
  • may run into performance issues
  • use when dimensions are huge in size

Examples

Share Economy Data Model

(e.g. Uber, airbnb)

Airbnb

The data model consists of five subject areas:

  • Countries & cities
  • Users & roles
  • Services & documents
  • Requests
  • Provided services
    OLTP:

OLAP
Star model:

  • user table
  • property table
  • service table
  • activity table
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
CREATE TYPE USER_ROLE AS ENUM (
    'visiter'
    'owner',
    'renter',
    'owner&renter'
);
CREATE TABLE user (
    user_id SERIAL
    age INT,
    gender CHAR(1),
    city VARCHAR(25),
    country VARCHAR(50),
    joined_date TIMESTAMP,
    active BOOLEAN,
    role USER_ROLE,
    PRIMARY KEY (user_id)
);
CREATE TABLE house (
    house_id SERIAL,
    owner_id INT NOT NULL,
    price INT NOT NULL,
    posted_date TIMESTAMP,
    room_type VARCHAR(255),
    parking BOOLEAN,
    features VARCHAR(255),
    PRIMARY KEY (house_id)
);
CREATE TABLE service (
    house_id INT NOT NULL REFERENCES house(house_id),
    renter_id INT,
    start_at TIMESTAMP,
    end_at TIMESTAMP,
    cost INT,
);
CREATE TABLE activity (
    user_id INT NOT NULL REFERENCES user(user_id),
    event_time TIMESTAMP,
    event_type VARCHAR(50),
    house_id INT NOT NULL REFERENCES house(house_id)
);

ride share app (Uber)

Reference