Notation for cardinality e.g. Crow's feet

[GoogleCodeExporter]

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 reported on code.google.com by dbr...@gmail.com on 20 Jan 2010 at 5:48

[GoogleCodeExporter]

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.

Original comment by ondrej.zara on 20 Jan 2010 at 6:55

[GoogleCodeExporter]

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.

Original comment by dbr...@gmail.com on 21 Jan 2010 at 6:40

[GoogleCodeExporter]

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.

Original comment by deansofer on 22 Jan 2010 at 8:57

[GoogleCodeExporter]

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.

Original comment by rossdav...@yahoo.com on 27 Jan 2010 at 9:17

[GoogleCodeExporter]

Original comment by ondrej.zara on 28 Jan 2010 at 12:13

• Changed state: Accepted

[GoogleCodeExporter]

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 :)

Original comment by dbr...@gmail.com on 30 Jan 2010 at 6:55

[GoogleCodeExporter]

> 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 ?

Original comment by geompse@gmail.com on 19 Mar 2010 at 6:59

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

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

Original comment by ober.14@osu.edu on 28 Mar 2010 at 3:55

Attachments:

• crow.patch

[GoogleCodeExporter]

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.

Original comment by deansofer on 5 Apr 2010 at 9:53

[GoogleCodeExporter]

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...

Original comment by deansofer on 1 Jun 2011 at 12:22

[GoogleCodeExporter]

Issue 171 has been merged into this issue.

Original comment by ondrej.zara on 29 Mar 2013 at 1:31