Perhaps a cross-reference table would be the most prudent way to go. One that has the ID from contacts, and the ID from members. This way you know who has converted, and can keep the table as small as necessary.
As an example (I use SQL Server, not MySQL):
CREATE TABLE Members
(
MemberID INT
, MemberData varchar(255)
);
CREATE TABLE Contacts
(
ContactID INT
, ContactDate datetime
, ContactData varchar(255)
);
CREATE TABLE MembersContactsXRef
(
MemberID INT
, ContactID INT
);
I would insert rows into MembersContactsXRef
each time a conversion occurred for a given member. Both fields in MembersContactsXRef
could be foreign keys.
Minimal solution
One radical solution might be to remove pin_inst
completely:
part ←────────── pin
↑ ↑
part_inst ←───── pin_inst
There is nothing in your question to suggest you actually need the redundant table. For pin
s associated to a part_inst
, look at the pin
s of the associated part
.
That would simplify the code to:
create table part ( -- using singular terms for table names
part_id bigserial primary key
);
create table pin (
pin_id bigserial primary key,
part_id bigint not null references part
);
create table part_inst (
part_inst_id bigserial primary key,
part_id bigint not null references part
);
But your comment made clear that we won't get away with that ...
Alternative if pin_inst
is needed
Including part_id
like you did is the simplest solution with foreign key constraints. You cannot reference a table “two tables away” with foreign key constraints.
But you can at least make do without "polluting" the primary keys. Add UNIQUE
constraints.
create table part (
part_id bigserial primary key
);
create table pin (
pin_id bigserial primary key,
part_id bigint not null references part,
unique(part_id, pin_id) -- note sequence of columns
);
create table part_inst (
part_inst_id bigserial primary key,
part_id bigint not null references part,
unique(part_id, part_inst_id)
);
create table pin_inst (
pin_inst_id bigserial primary key,
part_inst_id bigint not null,
pin_id bigint not null,
part_id bigint not,
foreign key (part_id, pin_id) references pin,
foreign key (part_id, part_inst_id) references part_inst
);
I put part_id
first in the unique constraints. That is irrelevant for the referential integrity, but it matters for performance. The primary keys already implement indexes for the pk columns. It's better to have the other column first in the multicolumn indexes implementing the unique constraints. Details under these related questions:
Related questions on SO:
Alternative with triggers
You could resort to triggers functions, which are more flexible, but a bit more complicated and error prone and a bit less strict. The benefit: you could do without part_inst.part_id
and pin.part_id
...
Best Answer
This mean you need NOT
(Child.name)
to be unique. You need in(Child.name, Child.parent_id)
to be unique.