0

In business process which I'm trying to model there are 2 entities which are in 1 to 1 or 0 relationship from both sides, for example:

entity_1 might or might not produce one and only one entity_2 record, and entity_2 can be produced without entity_1 participates in the process.

So my question is how that case should be modeled? All I found for now is rule that optional entity is child in 1 to 0 or 1 relationships. But what do I do if both are optional? My current assumption that I can make parent either of them, which I think more suitable.

1
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Commented Jan 24 at 20:49

1 Answer 1

0

I could be completely wrong but I think what you're looking for is a child linking table to represent when you have a complete 1-to-1 relationship instance.

Pseudo-code:

CREATE TABLE entity_1_entity_2
(
    entity_1_id INT,
    entity_2_id INT,
    PRIMARY KEY (entity_1_id, entity_2_id)
);

Both ID columns would make the primary key in this linking table. Then you'd want to create a foreign key from each ID field to it's related parent table, to enforce referential integrity. Finally you'd want to create a unique constraint on each ID field, individually, in the linking table too, to enforce only one instance of that ID from both parent tables can relate at any given time.

When a row exists in this table, that means there's a 1-to-1 relationship established between parent tables. Otherwise, when there's no row in this table, that implicitly means there's no relationship between the parents making them 0-to-0, 1-to-0, or 0-to-1.

2
  • thanks, this implementation definitely makes sense. I'll try to look into it
    – bst19
    Commented Jan 25 at 9:02
  • @bst19 Np, best of luck! Feel free to upvote and / or accept the answer if you find it helpful, to verify it for others with similar questions. Not required though.
    – J.D.
    Commented Jan 25 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.