HackerNews – Database Design

news, daily newspaper, press-1172463.jpg

A star schema is commonly used in designing a database with a central fact table surrounded by dimensional tables. Think of this as a main table with basic information, and other tables hold other information about certain rows in the main table. The main pros to having a star schema are that it helps with organizing different types of information and helps the user with processing and reading said information.

Here’s an example of a basic star schema with three tables transactions, products, and customers:

The transactions table is the fact table in this case as it has all information about specific transactions which we are looking to prioritize.

| transaction_id | product_id | customer_id | quantity | revenue | transaction_date |
|-----------------|------------|-------------|----------|---------|-------------------|
| 1               | 101        | 201         | 5        | 50      | 2023-01-15        |
| 2               | 102        | 202         | 8        | 120     | 2023-02-20        |
| 3               | 103        | 201         | 3        | 30      | 2023-03-10        |

Our two other tables are Products:

| product_id | product_name | category      |
|------------|--------------|---------------|
| 101        | Laptop       | Electronics   |
| 102        | Smartphone   | Electronics   |
| 103        | Desk Chair   | Furniture     |

And Customers:

| customer_id | customer_name | city         |
|-------------|---------------|--------------|
| 201         | John Doe       | New York     |
| 202         | Jane Smith     | Los Angeles  |
| 203         | Bob Johnson    | Chicago      |

It can be seen that these two tables describe specific things about say the product of each product_id and customers of each customer_id. These are keys that help connect the dimensional tables with the central fact table.