Can you have the IDs within a foreign key referenced twice in a record?-CodePudding

for example the parentID is a FK in a Child table. Because they child has two parents in the record it is recorded twice such as parentID 1 and 2. Is this possible.

CodePudding user response：

Yes, of course.

In relational design there can be many relationships between each pair of tables.

This is typical in money transfer tables that indicate "from which account" the money is coming from, and "to which account" the money is going to. There are many more examples, but you get the idea.

Also, depending on the design, each table can be a parent of the other, according to each relationship you decide to design.

CodePudding user response：

I'm not sure I understand your question but if I do then no this is not possible in the way you describe it, how ever what you'll do is create an intermediate table to connect multiple ID's from parent to one or more of child.

so you'll have some thing like this:

parent table -> ID, NAME --> id is PK
child table -> ID, NAME --> id is PK
intermidiateRelationTable -> ID, parentID, childID --> id is PK and parentID, childID are foreign keys each for its table.

that way you could join the data in the following way:

select * 
from intermidiateRelationTable irt join childTable ct 
on irt.childID = ct.ID
join parent p 
on irt.parantID = p.ID

not sure this is a good join but I hope you get the idea, this way in your example you'll get s2 rows, one for each parent.