MySQL Bugs: #45069: UML Relationship notation displays incorrect values

Bug #45069	UML Relationship notation displays incorrect values
Submitted:	25 May 2009 14:50	Modified:	28 Aug 2009 10:44
Reporter:	Steven Bakhtiari	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.2.1 alpha	OS:	Any
Assigned to:	Alfredo Kojima	CPU Architecture:	Any
Tags:	CHECKED, mandatory, notation, null, uml

Description:
When creating a relationship, say 1:n, by default the relationship shows as mandatory 1..* when in fact, by default, it would make more sense to show 0..* as there's many instances where the related table is not absolutely obliged to have 1 or more related rows. 0 or more is correct (both conceptually, and physically - there's no enforcement of 1 or more in the database).

This leads on to another issue, where if one toggles "NOT NULL" on a foreign key column in the related table, the relationship toggles between mandatory and not. This is also misleading, when allowing non-null values, you are in essense stating that the related table can have rows that _aren't_ related to the table that the foreign key references. Therefore, the value in the notation below the master table should change from 1 to 0..1.

This is quite easily explained with a diagram, which I have uploaded to the following URL: http://img297.imageshack.us/img297/3959/diagram.png

Allowing NULL values in the `customer_id` column in the `transaction` table should make the relationship to the `customer` table become non-mandatory (0..1) and shouldn't affect the notation values on the `transaction` table.

Once again, I feel it's worth repeating that 1:n relationships should be non-mandatory by default, though I imagine this setting is a side-effect of the mandatory-ness being defined by allowing NULL values.

If I've fluffed the explanation, please ask for clarification. =]

Regards

How to repeat:
Create 2 tables. Select the first relationship creation button from the toolbar (1:n) and create a link between the two tables. Toggle NotNull on the foreign key column and see the wrong value change.

Diagram used in explanation

Attachment: diagram.png (image/png, text), 10.31 KiB.

Thank you for the bug report. Are you using Windows`s release and could you provide the model example?. Thanks in advance.

Hi,

Sure, I'll upload a small test case (like the one in the screenshot). I spoke about the issue with akojima on irc and he seemed to think that with this particular notation, the value that is changed needs to be inverted.

In the example model I have attached, you can see that toggling the customer_id foreign key column in the transaction table, also toggles the mandatory nature of the relationship (1..* to 0..*). In this instance, this relationship shouldn't change. I also believe it should be 0..* (non-mandatory) by default, however, when creating a new 1:n relationship, it appears to be mandatory by default.

If I can help in any other way, please ask. =]

Regards

Example model to demonstrate the problem

Attachment: notation-example.mwb (application/octet-stream, text), 5.03 KiB.

Sorry Miguel, I forgot to state, I'm using the Linux version (5.2.1 alpha, compiled on Gentoo linux).

I do believe however, that this is a platform and version independent issue.

Regards

First of all these are two different feature requests in one bug report.

Anyway, both behaviours are not bugs.

Let me explain:

> When creating a relationship, say 1:n, 
> by default the relationship shows as mandatory 1..*
> when in fact, by default, it would make more 
> sense to show 0..* as there's many instances
> where the related table is not absolutely obliged 
> to have 1 or more related rows. 0 or
> more is correct (both conceptually, and
> physically - there's no enforcement of 1 or more
> in the database).

When you are using ERM technology in database desing then there is none 0:n. Your table always contains minimum of one row value and maximum of unlimited row values matching to the minimum of one and maximum of unlimited row values.

This is not a bug this is given by ERM technology.

> This leads on to another issue, where if one toggles "NOT NULL" on a 
> foreign key column in the related table, the relationship toggles
> between mandatory and not.

This also is not a bug. Also, it would be fatal, when a Foreign Key Column would not be allowed to be NULL by rules. There are lots of use cases especially when you use Triggers or Functions/Procedures to fill in the values after inserting for which the Foreign Key Column should have the possibility to allow NULL for the beginning.

Also you have some database design usecases where you fill the Foreign Key value long after the rest.

Hi Susanne,

I disagree - the only way the notation makes sense, is if you read it the wrong way round.

If you view the PNG image I have attached to this bug report, you will see a 1:n relationship between a customer and transaction table. The notation states that for each customer row in the table, there will be a _minimum_ of 1 row (or more) in the transaction row. This, I agree, may be more of a feature request, but I believe the relationship should be 0..* by default. Not only because it makes sense conceptually, but also because, by default, there's no way for the database to enforce this 1..* rule.

Secondly, the issue I wanted to highlight, was that if I change the `customer_id` foreign key column in the `transaction` table to _allow_ NULL values (which is a perfectly normal use-case scenario), the relationship notation changes to indicate that each row in the customer table may have ZERO or more transaction records. This is back-to-front. Technically, by allowing a NULL value in the foreign key column, we are stating that a row in the transaction table can exist, independent of the customer table. Which means that the relationship notation under the customer table should change from 1 to 0.

The reason both issues are mentioned in a single bug report, is because I believe the first issue (showing 1..* instead of 0..* by default) is part of this value being determined by allowed NULL values, which is itself, the bug I am reporting).

Regards

Explanation of UML notation

Attachment: uml-explanation.png (image/png, text), 8.02 KiB.

Hi Susanna, 

I have just attached another PNG image to explain the differences in the notation, hopefully it will make more sense than my previous explanation. If you look at this diagram, treat the customer table as entity A and the transaction table as entity B. In the database, if I set my foreign key column to allow NULL values, I am literally changing the relationship to match the third (3rd) diagram. In essense, I am allowing the customer table to have NO transaction records (perfectly normal) and at the same time, because I allow NULL values in the foreign key column of the transaction table, I am allowing transaction records to exist that do not relate to a customer table. Thus, the transaction table will have a relationship of 0..1 to the customer table. 

This is not the case in MySQL Workbench.

Allowing a NULL value in the foreign key column doesn't change the relationship to 0..1. In fact, it leaves that relationship untouched and instead changes the wrong relation. This seems to be the problem, it simply changes the _wrong_ relationship value. Perhaps because other notation formats are read this way, but in UML notation, it needs to be on the reverse side.

In any case, the current way it's done is incorrect - the notation is misleading.

Regards

I'm re-opening this report, it is definitely a bug.

Just a simple demonstration alone can prove this - create the two tables, make the relationship and you'll see that by default, the foreign key column allows NULL values. This is fine! However, even this is inconsistent with the way MySQL workbench treats the relationship notation.... try setting the foreign key column to disallow null values, then allow null values again. See the notation value change? So either there's a bug in the way the notation is handled anyway (which there is), or there's a bug where the default value is incorrect, or the new value that is set is incorrect. Take your pick. There's definitely a bug.

:|

The problem was that setting a FK to NULLable, should make the table referenced by the FK optional, not the table containing the FK itself.

This has been fixed in repository.

this was fixed and included in 5.2.2

An entry was added to the 5.2.2 changelog:

The UML relationship notation was incorrect.

When setting a Foreign Key to NULLable, the table referenced by the Foreign Key should be marked as optional. However, MySQL Workbench marked the table containing the Foreign Key itself as optional.