Notation for cardinality e.g. Crow's feet

[ondras]

From dbr...@gmail.com on January 20, 2010 06:48:51

This is not a bug, but a feature request. It'd be nice if there were some representation of cardinality. I use crow's feet, but it seems there are a lot of ways people do this. Take a look at http://en.wikipedia.org/wiki/Entity-relationship_model#Diagramming_conventions to see some examples.

Original issue: http://code.google.com/p/wwwsqldesigner/issues/detail?id=60

[ondras]

From ondrej.zara on January 19, 2010 22:55:44

How is the cardinality to be determined? Also, the linked article shows textual labels next to connectors, which are problematic due to the curved nature of wwwsqldesigner's connectors.

[ondras]

From dbr...@gmail.com on January 20, 2010 22:40:20

Textual labels aren't critical, but some kind of cardinality icon on either side of the FK relation line tends to make it easier to read complicated diagrams. It gives the relation a direction.

You can determine cardinality by determining which entity has the foreign key, as long as there are no 1:1 relations. If there are, I guess you need an option on the relation to indicate this.

[ondras]

From deansofer on January 22, 2010 00:57:24

There are only 3 kinds of relationships: 1-m, m-m and 1-1. A good designer should be able to recognize m-m easily, and foreign keys tell you which side has a 1, only thing that isn't implied is the 1-m vs 1-1, so if you solely add that symbol the rest shouldn't be necessary.

I suppose having a connector look like crows feet for that one relationship would be fine, but not necessary to markup the others.

[ondras]

From rossdav...@yahoo.com on January 27, 2010 13:17:05

Could the crowfoot be another object which you attach to the table first (perhaps with text options), and then the svg path object could attach to it instead of the table itself? If this was optional, it wouldn't make anything harder for those who don't need crow's feet, but it would be available for those who want to indicate this kind of relationship description on the diagram itself.

[ondras]

From ondrej.zara on January 28, 2010 04:13:15

Status: Accepted

[ondras]

From dbr...@gmail.com on January 30, 2010 10:55:08

Thinking about this some more, it boils down to one simple feature - indicating visually which side of the relationship has the foreign key. You can put crow's feet on the FK side or simply put an arrowhead on the other side.

I don't think it's worth complicating the interface as you suggest ross. Instead I think there should be one checkbox: Indicate directionality of relationships. This enables or disables them for the whole diagram at once.

Dean, of course you can just read the column names looking for a foreign key, but graphically marking the direction of the relation gives you the information at a glance, allowing you to understand the diagram faster. It's just a UI improvement. After all, we're looking at a diagram rather than DDL for a reason :)

[ondras]

From geompse@gmail.com on March 19, 2010 11:59:51

Thinking about this some more, it boils down to one simple feature - indicating visually which side of the relationship has the foreign key

That's sooo true ! Cardinality is a tools for the theory, the conceptual database.

Ex1 : m-m cardinality Entity : Users Entity : Tasks "One user has none, one or many tasks to do" "One task may be done by one or more users" Model doing this : Table : Users (id_user) Table : Tasks (id_task) Table : Links (id_link, id_user, id_task) Where in Links, id_user and id_task have appropriate FK constraints Has you can notice one "m-m" is in fact two "1-m"

Ex2 : 1-m cardinality Entity : Brains Entity : Neurons "One brain has none, one or many neurons" "One neuron belongs in only one brain" Model doing this : Table : Brains (id_brain) Table : Neurons (id_neuron, id_brain) Where in Neurons , id_brain appropriate FK constraint There is a more complicated notation to describe that a brain must have at least one neuron, but the model is the same.

Ex3 : 1-1 cardinality Entity : Poultry "One egg has one hen for mother and one cock for father" Model doing this : Table : Poultry (id_egg, id_hen, id_cock) Where in Poultry, id_hen and id_cock have appropriate FK constraints This is very theoric, since the very first egg had no hen for mother... or, was it the hen ?

[ondras]

From ober.14@osu.edu on March 27, 2010 20:55:21

Work in progress: SVG and non-smooth done, VML still untested.

Attachment: crow.patch

[ondras]

From deansofer on April 05, 2010 02:53:05

dbrand: When you use crows feet, you glance at a line, and then you glance at the end of the line for that 5 pixel end for the crows feet. You're telling me you can't look 10 pixels more to the right to just see if it's the primary id or not? Seriously, this is stuff you can tell from glancing. The primary id is 1 giant bold field. I hate most other ERD tools because of the unnecessary feature that bloats useability.

Like I said, the only notations handy are weak vs strong relationships and 1-1 vs 1-m. I would hate to see more buttons and more objects to click on and move around. If anything it should be a checkbox when editing the fk field.

[ondras]

From deansofer on May 31, 2011 17:22:01

I was looking at the relationship rendering code. Random thought (as a temp potential solution):

What if you set the RELATION_THICKNESS to 1 for 1-1 relationships? I realized people may not like what I mentioned about discerning relationships, but since so many lines overlap (on complex layouts) it would be nearly impossible to see the individual icons. Not to mention the inherit difficulty in rending and controlling more lines.

So I started theorizing what if we just used our own stylistic key to tell the difference? You could use CSS for the elements to put an asterisk on one of the corners.

Like, if you have 1-m, set the class to the element to be m-tl which means the many relationship is in the top left corner of the element. Since all segments end in a corner, just position a background image accordingly.

However after further thought I realized that unlike MySQL Workbench, the lines consistently overlap each other and it would be impossible to tell which relationship the icon belongs to. So what if we just tried to address the 1 unique case I mentioned above (1-m vs 1-1)?

What if we setup a checkbox for the relationship to allow users to specify 1-1 (since I would think this is usually rarer). If the relationship is 1-1, make the RELATION_THICKNESS = 1. It would make it very easy to spot out which lines have this property.

I had other ideas like having double lines (harder) or more strict management of relationship colors too (easier).

Food for thought...

[ondras]

From ondrej.zara on March 29, 2013 06:31:41

Issue 171 has been merged into this issue.