Class Table Inheritance (CTI)
This is a design pattern where each class in a hierarchy has its own database table, and the tables are linked by foreign keys. The relational databases don’t inherently support inheritance. However, CTI is a technique used to represent a hierarchy of classes in multiple tables by using inheritance relationships between tables.
For example
Suppose we have a table called vehicles that stores information about vehicles, and two tables called cars and trucks that store information about specific types of vehicles. We can use CTI to store all the vehicles in separate tables, with foreign keys that reference the parent table.
Advantages of the Class Table Inheritance are
- It preserves data integrity and consistency by using foreign keys and constraints.
- It avoids wasting space in the database by storing only relevant attributes for each subclass.
- It allows adding new subclasses easily by creating new tables.
Disadvantages of the Class Table Inheritance are
- It complicates data access and manipulation by requiring joins or unions between tables.
- It reduces performance and scalability by increasing the number of queries and joins.
- It makes ad-hoc reporting difficult because the data is spread across multiple tables.
Note: CTI is suitable for complex and deep class hierarchies where there is significant difference between subclasses.
CTI diagram:
Below is the explanation of the above diagram:
- The diagram shows three tables: Footballers, Cricketers, and Players. Each table has a primary key, which is a field that uniquely identifies each record in the table. The primary key is shown with an underline in the diagram. The tables also have foreign keys, which are fields that reference the primary key of another table. The foreign keys are shown with an arrow in the diagram.
- The Footballers table has fields for the player’s name and club. The name field is the primary key of the table, and it references the name field of the Players table. The club field stores the name of the football club that the player belongs to.
- The Cricketers table has fields for the player’s name, batting average, and bowling average. The name field is the primary key of the table, and it references the name field of the Players table. The batting average field stores the number of runs scored by the player per innings in cricket. The bowling average field stores the number of runs conceded by the player per wicket taken in cricket.
- The Players table has fields for the player’s name and sport. The name field is the primary key of the table, and it stores the full name of the player. The sport field stores the name of the sport that the player plays, such as football or cricket.
Design Patterns for Relational Databases
Relational databases are a way of storing and managing data in software development. They help you keep your data in order and find it quickly. But to use relational databases well, you need to follow some patterns that solve common problems and make your data work better. In this article, we will look at different patterns for relational databases, and explain how they can help you with specific issues and improve your database performance.
Important Topics for the Design Patterns for Relational Databases
- What are relational databases?
- Design Patterns for Relational Databases
- 1. Single Table Inheritance (STI)
- 2. Class Table Inheritance (CTI)
- 3. Entity-Attribute-Value (EAV)
- 4. Composite Key
- 5. Multipart Index
- 6. Materialized View
- 7. Many-to-Many Relationship
- 8. Caching
- 10. Queueing
- 11. Audit Log
- 12. Versioning