In order to demonstrate the power of a relational database, we'll create a database for a business that wishes to handle its merchandise, clients, orders, and staff (Alberto Hernández Chillón et al.,2019). Figure 1 shows Simple Entity Relationship Data Model.
Strategies for Schema design
When designing a database schema, there are several strategies that can be used to ensure that the resulting schema is efficient, scalable, and easy to maintain. Here are some common strategies for schema design:
Denormalization: Denormalization is the opposite of normalization, and involves adding redundant data to a database to improve performance. Denormalization can be useful for frequently accessed data that requires fast access times(Database Star).
Indexing: To increase the performance of queries, indexing includes establishing indexes on the columns of a table. Indexes can speed up query execution, but they can also make insert, update, and delete actions more laborious. The decision of which columns to index needs to be carefully thought out because it might significantly affect the database's performance.
Partitioning: Partitioning is the process of splitting a huge table into more manageable, smaller sections known as partitions. By minimizing the quantity of data that needs to be scanned, partitioning can enhance query performance. It can also make database management simpler by facilitating simpler backup and recovery (AWS).
Vertical partitioning: Based on the columns of a table, several tables are created by vertically partitioning the table. This can be helpful for tables with many columns because it can speed up queries by minimizing the amount of information that needs to be scanned.
Horizontal partitioning: Based on the rows of a table, several tables are created using horizontal partitioning. This can be helpful for tables with many rows because it can speed up queries by minimizing the amount of information that needs to be scanned.
Data archiving: Data archiving is the process of relocating data to a different storage system when it is no longer required for everyday activities. The amount of data that needs to be scanned is decreased, which can enhance the performance of searches.