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.