MySQL Bugs: #55724: Add visual clues to identifying relationships

Bug #55724	Add visual clues to identifying relationships
Submitted:	3 Aug 2010 21:55	Modified:	22 Aug 2014 15:58
Reporter:	Richard Watson	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S4 (Feature request)
Version:	5.2.25	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have been teaching data modeling for over two decades and learned early on that students have difficulty with identifying relationships. This problem is compounded to some extent in Workbench, despite being an excellent package that is quickly learned by students, because there is no visual connection between the type of line used to indicate whether a relationship is identifying or non-identifying. Why is an identifying relationship a solid line and not a dashed line? 

I create many data models on the board when teaching and drawing dashed and solid lines for the two different types of relationships is too cumbersome. Rather, I use a solid line for all relationships and add a '+' sign above and at the end of the arc for the entity that needs the identifying relationship. This makes visual sense and easily remembered by students. 

I request that you offer this feature in Data modeler for two reasons:
1. the visual signal of the '+' overcomes the lack of semantic connection to type of line (dashed or solid)
2. board or paper models will be visually similar to Workbench models. Many models get started on a whiteboard.

Thanks

Rick

How to repeat:
No a bug

So, you think that using solid line for identifying relationship and dashed line for non-identifying relationship (as Workbench does not) is a bad idea? 

I do not agree. Models that people create in Workbench often are large enough and do not fit on single screen/page. If any sign is used at some place to reflect identifying status, then it may be out of sight when someone checks relationships for some table on the big ERD. With dashed/solid lines used identifying status of relationship is clear, however long the line is and however complex the ERD is.

I now have a better understanding of why solid and dashed lines are used, but if this is the dominant representation logic, then it should be applied consistently. If the other entity in a relationship can't be seen because the model is large, then there are several pieces of information missing: the name of the entity, its attributes, and the type of relationship (e.g., 1:1, 1:m, m:m). Thus, if you can't see whether the relationship is 1:1 or 1:m, then maybe there should be 6 types of lines (identifying, 1:1, non-identifying 1:1, etc) so you can infer the type of relationship when one of the entities is not visible.

When I check a data model with novice clients, I am mainly interested in checking the type of relationship as this is where most mistakes are made so I am always forced to go across pages if I can't see the full relationship. Also, for novice clients or those who read a data models rarely, visual representations that make intuitive sense are preferable to arbitrary settings (such as dashed or solid).

You have a point, but I think a broader consideration of how people read data  models would favor my suggestion and improve the interface.

Hello Richard,

First of all our model layout is not given by standards. We looked here into several companies around the world who has had database models and we figured out that all these models not were drawn by using standards layout. They just were related to standard layouts.

Funny here is that independent from each other the model layouts were similar in all companies.

We overtook this layout and I once named this layout "practice oriented entity relationship model".

When I am teaching database basics at university then usually I first teach the standard layout and immediately after this I show my students the "practice oriented entity relationship model". Also for exam my students usually have to give me this kind of layout.

Workbench is designed for MySQL at the moment. So we optimized it for MySQL.

1:M with M ∈ ℕ

Means M > 0 <= ∞

This means that 1:1 ∈ 1:M

If a relation is 1:1 or 1:M with M > 1 M ∈ ℕ

you can see very easy in MySQL because for 1:1 a UNIQUE is needed.

Why?

3NF only accept relationships that reference to unambiguous entries.

InnoDB implemented this and only accept relationships referenced to columns that have UNIQUE constraint.

MySQL implemented UNIQUE constraints by using UNIQUE indexes.

This means the reference column must have an unique index.

In 1:1 ... when referenced column is unique by automatism referencing column has to be unique too.

When you use 1:1 in Workbench then it should create a unique index for the referencing column.

So, if relationship is 1:1 or 1:M with M > 1 M ∈ ℕ you can easily see by looking to the indexes. Also unique columns will have a red diamond.

I have to analyse if we have bugs in creating 1:1 relationships and also if we have bugs in dashed/solid lines.

Thanks Susan for the explanation of your choice of representations for MySQL Workbench. It is an excellent product, and I have used it with my classes for some time. I also encourage my colleagues and those who use my text on data management to use Workbench. I have noticed an improvement in the quality of my student's  models after shifting to Workbench.

My only quibble is that because modeling occurs on paper, whiteboards, and on computers, the representation of identifying relationships does not translate well between these environments. 

For example, if I am sketching a model on the board, I show an identifying relationship with a plus sign Later, we might discuss how we can make the relationship non-identifying. With a quick erase, I can get rid of the plus sign. Similarly, we might recognizing that we need to create an identifying relationship and I simply add a plus sign. Such quick changes don't work with solid and dashed lines in a manual environment. They work well if one stays within workbench, but modeling often starts on a whiteboard where groups can work more easily.

It took me some years of teaching to find a simple way to represent identifying relationships where there is a strong connection between the symbol, the plus sign, and its meaning (add a column to create a unique primary key). 

Please keep up the good work with Workbench. Along with MySQL, it is an important addition to my students' skill set.

Rick

I saw now what you meant.

Many thanks for writing a reasonable feature request.

Hi
Please create a mockup picture with the proposed notation, so we can see exactly what you mean and check whether it would work out in large diagrams.
Thanks

Illustration of proposed symbolic representation for identifying relationships

Attachment: identifying.pdf (application/pdf, text), 23.52 KiB.