Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Lithuania
    Posts
    2

    Lightbulb Unanswered: Table inheritance

    Hello all,

    Oracle 9i (rel2) ant WinXP. Devel.environment - SQL Navigator 4.

    My situation: I have a logical group o similar tables (~20 of them). Each of them contains columns ITEM_ID:INTEGER UNIQUE NOT NULL, NAME:VARCHAR2(5) NOT NULL, DESCRIPTION:VARCHAR2(80). Several tables have extra columns, like TYPE:CHAR or SIZE:INTEGER.
    The question is how can I orgainse table hierarchy so that I would have a parent table with common columns and a trigger (e.g. ON INSERT FOR EACH ROW), and group of child tables with derived properties. Obviously, I wish the trigger to be fired whenever I insert any record into any derived table.
    One more thing (I think this logically flows from the abovementioned) - I even wish to have a possibility to alter, say, NAME column (e.g. to enlarge it to VARCHAR2(6)) and to have it altered in all child tables.

    Is it possible? If it is - how do I implement this?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Table inheritance

    The solution is not to duplicate the common data into the child tables at all! Just put into the child tables the foreign key to the parent and the additional columns for the child.

    To see the "whole picture" for a child table now requires a join to the parent. Don't be afraid of that - relational DBMSs are designed to join data! For the convenience of writing queries you may want to "pre-join" the tables by creating a view for each child like this:

    create or replace view child1_view
    as select p.col1, p.col2, ...., c.col1, c.col2, ....
    from parent p, child1 c
    where c.parent_id = p.parent_id;

    Next, you may want to be able to insert a "whole" child record in one statement. To do that you will need to create "INSTEAD OF" triggers on each view like this:

    create or replace trigger child1_view_io
    instead of insert or update or delete on child1_view
    begin
    if inserting then
    insert into parent( parent_id, ... ) values (:new.parent_id, ... );
    insert into child1( parent_id, ... ) values (:new.parent_id, ... );
    elsif updating
    ... -- You get the idea?
    end;
    /

    Now you can insert, update and delete the view and the changes propagate to the underlying tables.

    Finally, having said all that, the most efficient solution may be to have just one table that combines all your 20 current tables:

    create table everything
    ( id integer not null primary key
    -- "Discriminator" column type indicates which type of entity this is
    , type varchar2(10) not null check (type in (...))
    -- Common data
    , common_col1 ...
    , common_col2 ...
    ...
    -- Subtype 1 specific data
    , type1_col1 ...
    , type1_col2 ...
    ...
    -- Subtype 2 specific data
    , type2_col1 ...
    , type2_col2 ...
    ...
    ...
    -- Constraints to ensure columns populated are relevant to subtype
    , constraint type1_check check ((type='TYPE1' and type1_col1 is not null and ...) or (type1_col1 is null and ...))
    ...
    );

    Again, you could create a view for each subtype for convenience of querying. The data could even be partitioned on type so that queries can eliminate all but one partition when performing full scans.

  3. #3
    Join Date
    Nov 2003
    Location
    Lithuania
    Posts
    2

    Re: Table inheritance

    Originally posted by andrewst
    For the convenience of writing queries you may want to "pre-join" the tables by creating a view for each child like this:

    create or replace view child1_view
    as select p.col1, p.col2, ...., c.col1, c.col2, ....
    from parent p, child1 c
    where c.parent_id = p.parent_id;

    Thank you andrewst - this is absolutely correct and great idea in my case

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •