Home » Other » General » data integrity for 1 to 1..n relationship
data integrity for 1 to 1..n relationship [message #224005] Mon, 12 March 2007 11:01 Go to next message
hellcat
Messages: 84
Registered: January 2006
Location: Zug, Switzerland
Member
Hi

I would like some senior designer opinion on following issue:

A record in Entity B can only exist if one or more records in Entity A exists. => Entity-B 1 to Entity-A 1..n

to provide my real-live problem:
One or more models describe a method. A method can only be inserted into the METHOD table when there exists one or more models in the MODEL table describing this method.

how would you ensure data integrity with foreign keys only? Or would it make good sense to use triggers to check for existence of models?

create table model (
    model_id number
  , model_attribute_1 number
  -- ...
  , model_attribute_n number
  , constraint pk_model primary key (model_id)
);

create table method (
    method_id number
  , method_attribute_1 number
  -- ...
  , method_attribute_n number
  , constraint pk_method primary key (method_id)
);


now, I can't just add MODEL_ID as an attribute to method and add a foreign key since I probably get several models for the same method.

any suggestions?

many thanks!
Re: data integrity for 1 to 1..n relationship [message #224122 is a reply to message #224005] Tue, 13 March 2007 03:03 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This one comes up from time to time. I still haven't seen a good way to do it.

Its possible to set up any number of different systems, but they have one thing in common: they only validate rows as they are inserted/updated. Unlike RI constraints, there does not seem to be any way to have a set of constraints that GUARANTEE that the current contents of the tables satisfy the property of 1:n where n>=1.

If this is true (I'm not saying someone can't come up with a splendid solution, I just haven't heard of one), then it means you are enforcing the constraint in the application layer (even if the application code is stored on the database). For this reason, you should choose the most simple and intuitive method of enforcement. A stored procedure that maintains both tables at once is a good example. Complex arrays of triggers and views and supplementary tables are a bad way of doing it.

You could probably do it with Object Relational features (nested tables). I haven't tried. This is not a good reason to go Object Relations if you are not already using those features, as they create a host of other difficulties.

Ross Leishman
Re: data integrity for 1 to 1..n relationship [message #224131 is a reply to message #224122] Tue, 13 March 2007 03:49 Go to previous messageGo to next message
hellcat
Messages: 84
Registered: January 2006
Location: Zug, Switzerland
Member
Hi Ross

Thanks a lot for your explanation. I currently do make sure the data integrity by stored procedures -> transaction api's.
Was wondering if anybody knows a savvy design to solve this problem with RI constraint.

Cheers
Re: data integrity for 1 to 1..n relationship [message #224191 is a reply to message #224131] Tue, 13 March 2007 06:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
By all means see if anyone can come up with anything; Oracle is full of wonderous little-known features. But don't hold your breath, and when they do start flooding in, go over them carefully and work out for yourself whether they are constraining the data of constraining the DML.

Ross Leishman
Re: data integrity for 1 to 1..n relationship [message #224278 is a reply to message #224191] Tue, 13 March 2007 12:49 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
worth a read

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:42170695313022
Re: data integrity for 1 to 1..n relationship [message #224442 is a reply to message #224278] Wed, 14 March 2007 05:31 Go to previous messageGo to next message
hellcat
Messages: 84
Registered: January 2006
Location: Zug, Switzerland
Member
how about that:

create table global_method (
    method_id number
  , db_id number not null
  , method_ac varchar2(15) not null
  , model_id number not null
  , mm_id number not null
  , short_name varchar2(1000)
  , long_name varchar2(4000)
);

create unique index pk_global_method on global_method (method_id);

create unique index ix_global_method_01 on global_method (db_id, method_ac);

alter table global_method add constraint pk_global_method primary key (method_id)
using index pk_global_method;

alter table global_method add constraint uc_global_method_01 unique (db_id, method_ac)
using index ix_global_method_01;

create table hmm_model (
    model_id number
  , model_type varchar2(30) not null
  , hmm varchar2(64)
  , hmmer varchar2(20)
  , mu float(126)
  , lambda float(126)
  , status char(1) not null
);

create unique index pk_hmm_model on hmm_model (model_id);

alter table hmm_model add constraint pk_hmm_model primary key (model_id)
using index pk_hmm_model;


drop table method_model;
create table method_model (
    mm_id number
  , model_id number not null
  , method_id number not null
);

create unique index pk_method_model on method_model (mm_id)
tablespace dev_idx;

create unique index ix_method_model_01 on method_model (model_id, method_id)
tablespace dev_idx;

alter table method_model add constraint pk_method_model primary key (mm_id)
using index pk_method_model;

alter table method_model add constraint cu_method_model_01 unique (model_id, method_id)
using index ix_method_model_01;

alter table global_method add constraint fk_global_method_01 foreign key (db_id)
references dbversion (db_id);

alter table method_model add constraint fk_method_model_01 foreign key (model_id)
references hmm_model (model_id);

alter table method_model add constraint fk_method_model_02 foreign key (method_id)
references global_method (method_id);

alter table global_method add constraint fk_global_method_02 foreign key (mm_id)
references method_model (mm_id) deferrable initially deferred;


I played around with this and it seems to fulfill my requirements...what do you think?
Re: data integrity for 1 to 1..n relationship [message #224663 is a reply to message #224442] Thu, 15 March 2007 01:14 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Not sure I get what this is doing, but it doesn't seem to stop you from creating an HMM_MODEL row without populating the other tables.... Confused I thought that was the purpose...

Ross Leishman
Re: data integrity for 1 to 1..n relationship [message #224741 is a reply to message #224663] Thu, 15 March 2007 05:29 Go to previous messageGo to next message
hellcat
Messages: 84
Registered: January 2006
Location: Zug, Switzerland
Member
na, the goal is to prevent records in GLOBAL_METHOD for which no corresponding record exists in HMM_MODEL whereas several HMM_MODEL records can refer to a GLOBAL_METHOD record...
Re: data integrity for 1 to 1..n relationship [message #224902 is a reply to message #224741] Thu, 15 March 2007 20:10 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
OK. So a Method can have one or more models, but models can exist independantly of methods. So rather than 1 to 1..n as you placed in the title, its actually 0..n to 1..m, or...
METHOD >0---|< MODEL


So we normalise that with an associative entity:
METHOD ||---|< MODELMETHOD >0---|| MODEL


In order to enforce the ||---|< relationship, you have denormalised one of the possibly many MODELMETHOD keys into METHOD.

The problem with this is if a model has (say) 5 model methods: MM_ID 1,2,3,4 and 5. MM_ID 1 is denormalised onto METHOD, but then you want to delete MM_ID=1 from MODELMETHOD. This means you have to update the MM_ID in METHOD to one of the other 4 values.

You can get into similar or worse trouble if you update the method_id of one of the rows in MODELMETHOD.

Denormalising is not a robust solution to enforcing constraints. You somewhat solved your 1 to 1..n constraint, but in doing so you've complicated the data model and introduced another constraint that needs to be enforced in the application layer.

If you are going to have some procedural enforcement anyway, you may as well keep it simple and enforce the mandatory child relationship per Tom Kyte's example.

Ross Leishman
Previous Topic: developer and oracle 8.1.7.0.0
Next Topic: 10g Client Form Feed
Goto Forum:
  


Current Time: Sun Dec 04 20:47:16 CST 2016

Total time taken to generate the page: 0.07420 seconds