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?
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
if inserting then
insert into parent( parent_id, ... ) values (:new.parent_id, ... );
insert into child1( parent_id, ... ) values (:new.parent_id, ... );
... -- You get the idea?
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.