Database design question

[RockabillyJim]

I have two tables with foreign keys to the same parent table and a junction table between them to map a many to many relationship. I want to know how best to enforce data integrity in the junction table so that both parents' parent is the same.

Example:

CREATE TABLE Parent
(
ParentCode char(3) NOT NULL,
Name varchar(50) NOT NULL,
PRIMARY KEY (ParentCode)
);

CREATE TABLE Child1
(
Child1Code char(3) NOT NULL,
Name varchar(50) NOT NULL,
ParentCode char(3) REFERENCES Parent(ParentCode)
PRIMARY KEY (Child1Code)
);

CREATE TABLE Child2
(
Child2Code char(3) NOT NULL,
Name varchar(50) NOT NULL,
ParentCode char(3) REFERENCES Parent(ParentCode)
PRIMARY KEY (Child1Code)
);

CREATE TABLE ChildJunction
(
Child1Code char(3) NOT NULL,
Child2Code char(3) NOT NULL,
Attribute int NOT NULL,
PRIMARY KEY (Child1Code, Child2Code)
);

How do I ensure that the Child1 and Child2 in ChildJunction have the same Parent?

The best I have so far is that ChildJunction includes a ParentCode column, I create a UNIQUE constraint on Child1Code + ParentCode in Child1 and a UNIQUE constraint on Child2Code + ParentCode in Child2 and the FOREIGN KEYS from ChildJunction are ChildJunction.Child1Code = Child1.Child1Code AND ChildJunction.ParentCode = Child1.ParentCode and ChildJunction.Child2Code = Child2.Child2Code AND ChildJunction.ParentCode = Child2.ParenCode.

But that leaves me with redundant data in ChildJunction (i.e. ParentCode).

Am I missing something? This must be a common problem? have I designed the Child tables wrong? Or the Parent table?

Any help gratefully received...

Thanks,
Simon

[dickersonka]

Normally what you would do is have a single child table, with an additional column for child_type_id

CREATE TABLE ChildType(
CHILD_TYPE_ID int NOT NULL,
CHILD_TYPE_NAME VARCHAR(20),
PRIMARY_KEY(CHILD_TYPE_ID)
);

CREATE TABLE Children(
ChildCode char(3) NOT NULL,
Name varchar(50) NOT NULL,
CHILD_TYPE_ID int NOT NULL REFERENCES ChildType(CHILD_TYPE_ID),
ParentCode char(3) REFERENCES Parent(ParentCode)
PRIMARY KEY (ChildCode)
);

populate ChildType with your 2 entries of what child 1 and 2 are, lets say with CHILD_TYPE_ID's of 1 and 2

this way fk are in place, and if you need to query what would be in the old 'Child2' you can use

-- child 2 query
select * from Children where CHILD_TYPE_ID = 2;

-- child 1 query
select * from Childrean where CHILD_TYPE-ID = 1;