1 to 1 relationship with exactly one of multiple tables

OK, I hope I'll be able to make clear what my problem is: I have a database with 5 tables. Let's call them A and B, V_1, V_2, and V_3. A and B represent a list of things to be done. These actions are described in the V_i tables. Now, A represents sort of a template of stuff that has to be done with a certain type of item. B, on the other hand, describe开发者_JAVA百科s what has to be done (or has been done) with a concrete instance if the abstract item described by A. So in OOP terminology one might say that A represents a class and B represents an instance of A. Whenever something is inserted into table B, the related data from table A is copied, so that it can be modified for that specific item without affecting A.

Okay, so here is the actual problem: How do I model this properly? My main concern is that each record in V_i must not be linked to both A and B. It has to be a 1 to 1 relationship with EITHER A OR B. Also, V_i and V_j must not be linked to the same record in A or B. I have no clue how to do this properly. The current structure looks like this:

A and B have a PK called ID. Each V_i also has a PK called ID and two FKs that referene A or B, let's call them A_ID and B_ID. Now, the current implementation ensures that either A_ID or B_ID is NULL, but not both. However, I was wondering if there is a better way to do this. Additionally, there is the problem that multiple V_i could reference the same entry in A or B.

So, I hope my problem is clear. Is there a way to properly model this with relational databases without relying on external code to enforce the constraints? Thanks for your input in advance.

Best regards David

In relational theory, one-to-one relationships are generally translated to a single table in the physical model. This single table would contain rows from both tables and you would use check constraints to determine the type of the row. This is by far the simplest way to get reliable 1-to-1 relationships.

First thing: when designing a database, you express relations between records not tables. You are expressing your problem with an OO point of view. This paradigm cannot be used to design tables (SQL being a declarative language).

Otherwise, you can add constraints on your table ensuring your predicate.

Maybe Oracle offers other possibilities I don't know.

The most common way to model the class - instance relationship in rdbs is Class = table Instance = row

Think about it: you insert a new row for each new instance; where you do not insert data, defaults are inserted, which give you class data; and triggers give you class-level behaviours.

Alternatively, give A and B the same primary key, and set the PK of B to be an FK to the PK of A. When a row is included in B, the DBMS will check that a "parent" row exists in A. Probably needs drawing

+--------+   +--------+
|Table A |   |Table B |
+--------+   +--------+
|id (PK) |<--|id* (PK)|
|col1    |   |colB1   |
| ...    |   | ...    |
+--------+   +--------+

Preface: This is a bad design, as others have noted.

Assumptions:

create table a (a_id number primary key); 

create table b (b_id number primary key); 

create table v1 
(v1_id number primary key, a_id number references a, b_id number references b);

create table v2
(v2_id number primary key, a_id number references a, b_id number references b);

create table v3
(v3_id number primary key, a_id number references a, b_id number references b);

Mandating that in any of the V_i tables that exactly one of the ids from A or B is required (but not both) is pretty easy.

alter table V1
  add constraint v1_check check
  (    (a_id is null and b_id is not null)
    or (a_id is not null and b_id is null)
  );

If you want to extend that constraint so that exactly one of the ids from A or B is present and that value exists in one and only one row:

create unique index v1_check_unique on v1  ( coalesce (a_id, b_id) );

The hard part is making sure that the ids from A and B exist in one and only one of the V_i tables. That can't be done at DML time, but it can be enforced at commit time.

create materialized view log on v1 with rowid;
create materialized view log on v2 with rowid;
create materialized view log on v3 with rowid;
CREATE MATERIALIZED VIEW CROSS_TABLE
REFRESH FAST ON COMMIT
AS
  SELECT V1_ID AS V_ID, 'V1' AS TABLE_NAME, ROWID AS ROW_ID, 
         COALESCE (A_ID, B_ID) AS OTHER_ID FROM V1
  UNION ALL
  SELECT V2_ID AS V_ID, 'V2' AS TABLE_NAME, ROWID AS ROW_ID, 
         COALESCE (A_ID, B_ID) AS OTHER_ID FROM V2
  UNION ALL
  SELECT V3_ID AS V_ID, 'V3' AS TABLE_NAME, ROWID AS ROW_ID, 
         COALESCE (A_ID, B_ID) AS OTHER_ID FROM V3
/

ALTER TABLE CROSS_TABLE ADD CONSTRAINT CROSS_TABLE_UNIQUE UNIQUE (OTHER_ID);

This appears to work - but not as awesomely as you'd hope. Oracle can't enforce that uniqueness across the tables at statement time because session A isn't allowed to take into account any other changes other sessions might be making. It can only enforce that uniqueness at commit time.

The following test case fails when run against empty tables - and rolls back the entire transaction, as it can't deduce which is causing the failure. Caveat emptor.

INSERT INTO A VALUES (1);
INSERT INTO B VALUES (1);
INSERT INTO V1 (V1_ID, A_ID, B_ID) VALUES (1, 1, NULL);
INSERT INTO V2 (V2_ID, A_ID, B_ID) VALUES (1, 1, NULL);
COMMIT;