mysql - How to do Inheritance Modeling in Relational Databases? - Stack Overflow
There are two ways we can do code reuse either by implementation of inheritance (IS-A relationship), or object composition (HAS-A. The rest of this article describes one of the fields that I tried to improve recently — Inheritance design in relational databases. Your question is typically too broad to fit in a post here, but I'll draw out some lines that might help you to get a start. A vehicle can be either a.
How to Model Inheritance in a Relational Database
The name of an associative table is typically either the combination of the names of the tables that it associates or the name of the association that it implements. In this case I chose EmployeeTask over Assigned. Notice the multiplicities in Figure The rule is that the multiplicities "cross over" once the associative table is introduced, as indicated in Figure A multiplicity of 1 is always introduced on the outside edges of the relationship within the data schema to preserve overall multiplicity of the original relationship.
The original relationship indicated that an employee is assigned to one or more tasks and that a task has zero or more employees assigned to it. In the data schema you see that this is still true even with the associative table in place to maintain the relationship. Assume that an employee object is in memory and we need a list of all the tasks they have been assigned. The steps that the application would need to go through are: The Select statement is run against the database.
The data records representing these tasks are marshaled into Task objects. Part of this effort includes checking to see if the Task object is already in memory. If it is then we may choose to refresh the object with the new data values this is a concurrency issue.
relations - Database relationship inheritance - Database Administrators Stack Exchange
A similar process would have been followed to read in the employees involved in a given task. To save the relationship, still from the point of view of the Employee object, the steps would be: Add Update statements for any task objects that have changed. Add Insert statements for the Task table for any new tasks that you have created.
Add Insert statements for the EmployeeTask table for the new tasks. Add Delete statements for the Task table any tasks that have been deleted. This may not be necessary if the individual object deletions have already occurred. Add Delete statements for the EmployeeTask table for any tasks that have been deleted, a step that may not be needed if the individual deletions have already occurred.
Add Delete statements for the EmployeeTask table for any tasks that are no longer assigned to the employee.
Many-to-many relationships are interesting because of the addition of the associative table.
Two business classes are being mapped to three data tables to support this relationship, so there is extra work to do as a result. When mapping this to a relational database you need to add an addition column to track this information. The database schema, also depicted in Figure 1includes the column OrderItem. ItemSequence to persist this information. Although this mapping seems straightforward on the surface, there are several issues that you need take into consideration.
These issues become apparent when you consider basic persistence functionality for the aggregate: Read the data in the proper sequence. The scaffolding attribute that implements this relationship must be a collection that enables sequential ordering of references and it must be able to grow as new OrderItems are added to the Order. In Figure 2 you see that a Vector is used, a Java collection class that meets these requirements.
How To: Model Inheritance in Databases
As you read the order and order items into memory the Vector must be filled in the proper sequence. If the values of the OrderItem. ItemSequence column start from 1 and increase by 1 then you can simply use the value of the column as the position to insert order items into the collection.
You have an order with five order items in memory and they have been saved into the database. You now insert a new order item in between the second and third order items, giving you a total of six order items.
With the current data schema of Figure 1 you have to renumber the sequence numbers for every order item that appears after the new order item and then write out all them even though nothing has changed other than the sequence number in the other order items.
Because the sequence number is part of the primary key of the OrderItem table this could be problematic if other tables, not shown in Figure 1refer to rows in OrderItem via foreign keys that include ItemSequence.
When do you update sequence numbers after rearranging the order items? Whenever you rearrange order items on an order, perhaps you moved the fourth order item to be the second one on the order, you need to update the sequence numbers within the database.
- Post navigation
- Understanding Object Inheritance
Do you update sequence numbers after deleting an order item? If you delete the fifth of six order items do you want to update the sequence number for what is now the fifth item or do you want to leave it as it. The sequence numbers still work — the values are 1, 2, 3, 4, 6 — but you can no longer use them as the position indicators within your collection without leaving a hole in the fifth position.
Description of the illustration rtbrlf. By default, queries performed on the root class return instances of the root class and its instantiable subclasses. However, you can also configure the root class to return only instances of itself, without instances of its subclasses when queried. All class types beneath the root class inherit from the root class. Branch Class Branch classes have a persistent superclass and subclasses. By default, queries performed on the branch class return instances of the branch class and any of its subclasses.
As with the root class, you can configure the branch class to return only instances of itself, without instances of its subclasses when queried. All classes below the branch class inherit attributes from the branch class, including any attributes the branch class inherits from classes above it in the hierarchy. Leaf Class Leaf classes have a persistent superclass in the hierarchy, but do not have subclasses.
Queries performed on the leaf class return only instances of the leaf class. Class Indicators A class indicator is a mechanism for determining the class or type of an object.
For example, a Person table may include an indication of whether the person represented by the table row is an Employee or a Manager.