M:N resolution table has 1:N relationship with another table

[baud]

Hi,
Let's say I have a M:N relationship between table A and table B.
I create the C table with primary key [idA & idB].

I want to create a 1:N relation ship between table C and a table D.
So I should have [idA & idB] acting as a foreign key in table D.

idA is autonumber primary key in table A; idB is autonumber primary key
in Table B.
In table C, idA and idB are declared as long integers, and the primary
key is [idA & idB].

What about the foreign key [idA & idB] in table D? I guess I should
have to create a field that is the concatenation of two long integers,
so I am in trouble here.
So I did the following: create an autonumber idC in table C as a simple
key without duplicates, and add idC as a foreign key to table D; of
course, I also added individually idA and idB to table D, and created
some keys on them.

Did I follow the right approach to solve my problem?

 

[peregenem]

Let's say I have a M:N relationship between table A and table B.
I create the C table with primary key [idA & idB].

I want to create a 1:N relation ship between table C and a table D.
So I should have [idA & idB] acting as a foreign key in table D.

idA is autonumber primary key in table A; idB is autonumber primary key
in Table B.
In table C, idA and idB are declared as long integers, and the primary
key is [idA & idB].

What about the foreign key [idA & idB] in table D? I guess I should
have to create a field that is the concatenation of two long integers,
so I am in trouble here.

If by 'concatenation' you mean a compound key

CREATE TABLE A (
idA INTEGER IDENTITY NOT NULL PRIMARY KEY);

CREATE TABLE B (
idB INTEGER IDENTITY NOT NULL PRIMARY KEY);

CREATE TABLE C (
idA INTEGER NOT NULL REFERENCES A (idA)
ON DELETE CASCADE
ON UPDATE CASCADE,
idB INTEGER NOT NULL REFERENCES B (idB)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (idA, idB));

CREATE TABLE D (
idA INTEGER NOT NULL,
idB INTEGER NOT NULL,
FOREIGN KEY (idA, idB) REFERENCES C (idA, idB)
ON DELETE CASCADE
ON UPDATE CASCADE);

 

[baud]

Thanks Perege...

So, if I understand you the right way, there's no need to create a
redundant idC in table C and (as a foreign key) in table D to make the
1:N relationship between those tables: iDA and idB are all I need to
create this relationship.

 

[peregenem]

if I understand you the right way, there's no need to create a
redundant idC in table C and (as a foreign key) in table D to make the
1:N relationship between those tables: iDA and idB are all I need to
create this relationship.

Correct.

 

[Tim Ferguson]

Let's say I have a M:N relationship between table A and table B.
I create the C table with primary key [idA & idB].

I want to create a 1:N relation ship between table C and a table D.
So I should have [idA & idB] acting as a foreign key in table D.

Sorry: I find all this abstracted stuff really hard to imagine. Can I
invent a scenario?

Borrowers(*CustomerID, FName, LName, Address, etc)

Books(*ISBN, Authors, Title, AccessionNumber, etc)

Loans(*CustomerID, *ISBN, DateOut, DateIn)
// yes I know it's unlikely that a customer can only borrow
// a book once, but it's late okay?

ReminderLetters(*DocumentID, CustomerID, ISBN, SignedBy, etc)
PertainsTo FOREIGN KEY (CustomerID, ISBN) REFERENCES Loans

I guess I should
have to create a field that is the concatenation of two long integers,
so I am in trouble here.

You can create a two-field relationship in the relationships window by
control-clicking the two fields and dragging them both to the target
window.

If you want to use SQL-DDL, then it's documented in the help files for
the CONSTRAINT clause.

Hope that helps


Tim F