CODEWITHSUNDEEP
databasemysql-workbenchentity-relationship
Duy
Duy

Reputation: 803

Fully optional one to one relation in MySQL workbench

Fully optional one to one relation in MySQL workbench?
I'm only able to create a partially optional one to one relation.
My case is:

A GROUP can be assigned a PROBLEM
A PROBLEM can be assigned to a GROUP

EDIT1: enter image description here

EDIT2: Maybe a better question would be if fully optional one to one relations should be avoided?

Upvotes: 1

Views: 1799

Answers (1)

Marc Compte
Marc Compte

Reputation: 4819

Let's see if any of this addresses your issue.

A GROUP can be assigned a PROBLEM

A PROBLEM can be assigned to a GROUP

Starting with a structure such as:

PROBLEM
id   |   title
 1   |   Prob1
 2   |   Prob2

GROUP
id   |   title
 1   |  Group1
 2   |  Group2

What is also important is to know whether a GROUP can be assigned more than one problem at a time or not. And whether one same problem can be assigned to more than one GROUP.

Let's say there is a strict optional 1:1 relationship. This means a group cannot have 2 problems assigned at the same time and that 1 same problem cannot be assigned to 2 groups.

A strict 1:1 would be implemented by adding the PK of table A as a FK of table B. If the FK is nullable then you will notice this is already an optional 1:1, as you may leave empty cells indicating 0 problems assigned (or 0 groups assigned).

PROBLEM
id   |   title   
 1   |   Prob1   
 2   |   Prob2   

GROUP
id   |   title   |   problem
 1   |  Group1   |     2
 2   |  Group2   |    null

In this example Group2 has been assigned no problem. Group1 has been assigned Prob2 and Prob1 has been assigned to no group.

You are not forced to assign anything but everything may have a 1:1 relationship.

This structure may imply quite a few empty (null) values. This is not best practices but would do the job. If you want to avoid null values then you may have to go for a N:M implementation.

PROBLEM
id   |   title   
 1   |   Prob1   
 2   |   Prob2   

GROUP
id   |   title
 1   |  Group1
 2   |  Group2

GROUP_PROBLEM
group   |   problem
  1     |      2

With this implementation alone you may have 1 group be assigned more than 1 problem and have 1 same problem be assigned to more than 1 group. But if you define a UNIQUE index for each of the two fields (group and problem) then you should fix this.

Upvotes: 1

Related Questions