SQL Oracle Inheritance Relational Database

Question

Here is the image:

Here's how far I went into encoding:

CREATE TYPE appointment_list_type AS TABLE OF REF appointment_type; / CREATE OR REPLACE TYPE person_type AS OBJECT ( personID NUMBER, Surname varchar2(10), Forname varchar2(10), dateOfBirth date, AddressLine1 varchar2(30), AddressLine2 varchar2(30), Town varchar2(10), contacTel1 varchar2(10), contacTel2 varchar2(10)) NOT FINAL; / CREATE TYPE applicant_type UNDER person_type( applicantID NUMBER, maxPrice number(7,2), desiredArea varchar2(10), Attends appointment_list_type ); / CREATE TYPE salesperson_type UNDER person_type( salespersonID NUMBER, manager varchar2(10), Makes appointment_list_type ); / 

This creates the types of people who divide it into the inheritance of the Seller and the Applicant.

 CREATE TYPE appointment_type AS OBJECT( appointmentID NUMBER, Appdate date, Apptime timestamp, appointmentType varchar2(10), levelOfInterest varchar2(10), offerMade varchar2(10), Made_by REF salesperson_type, Attends_by REF applicant_type ); / 

This is the type of destination, links link to them together.

To create a table:

 CREATE TABLE person_table OF person_type ( personID PRIMARY KEY NOT NULL) NESTED TABLE Attends STORE AS attend_meeting_table; CREATE TABLE applicant_table OF applicant_type ( personID PRIMARY KEY NOT NULL) NESTED TABLE Attends STORE AS attend_meeting_table; CREATE TABLE salesperson_table OF salesperson_type ( personID PRIMARY KEY NOT NULL) NESTED TABLE Makes STORE AS makes_meeting_table; CREATE TABLE appointment_table OF appointment_type ( appointmentID PRIMARY KEY NOT NULL, SCOPE FOR (Made_by) IS person_table, SCOPE FOR (Attends_by) IS person_table); 

Also here is some code of what I did, now here I am the question:

How does inheritance work with doing 1st for many directly in the Destination?

I am really confused by this. Can any of me help me on how to do this?

Answer 1

phuh, I think I finally realized what bothers you ...

Currently, the REFd assignments in request_table and salesperson_table are completely independent. This means that applicants can meet sellers who are actually dating someone else :)

Of course, you want all appointments to be saved in the destination table.

This is an ideal use case for viewing objects. You do not need these object tables at all. Relational tables are much easier to manage.

Just create regular tables and then objects, as for SALESPERSON:

 create view ov_salesperson as (select personID, salespersonID, SALESPERSON_TYPE (personID Surname, Forname, dateOfBirth, AddressLine1, AddressLine2, Town, contacTel1, contacTel2, salespersonID, manager, CAST (MULTISET (Select appointment_type (appointmentID, Appdate, Apptime, appointmentType, levelOfInterest, offerMade, salesperson_id, applicant_id ) From appointment_table A Where A.salesperson_id = S.salesperson_id ) as appointment_list_type ) ) as salesperson_obj from salesperson_table S );