cannot define circular relation. pretty sure it's a bug.

[V T]

Hi,
using latest Visio (EI) I am creating ER diagram for having MS SQL 2005 as a
target database.

When I have a table which is a classic tree:
NodeID(int, not null), ParentID (int, null) . Primary key is NodeID
I can define relationship within table itself between NodeID and ParentID.
No problem here.

Then I need to change design a bit. There is an additional component to the
primary key called SectionID.
Now fields of the table are: SectionID(int, not null),NodeID(int, not null),
ParentID (int). Primary key is SectionID,NodeID
Now Visio does not let me associate SectionID in parent to SectionID in
child table when I try to create relationship to itself.

If I create same table in SQL it creates no problem. If I reverse engeneer
this table into Visio it is transferred OK. And again in this case in Visio
Disconnect button is not available for SectionID in parent and child.

So, it looks like Visio does not behave properly when it has to deal with
circular relationship and a fileld on the parent table side needs to be
equal to the same field on the child table side.

VT

 

[--CELKO--]

When I have a table which is a classic tree: NodeID(int, not null), ParentID (int, null) . Primary key is NodeID <<

You might want to get a copy of TREES & HIERARCHIES IN SQL for other
ways to model tree structures. What you have now is an adjacency list
model and has some problems.

 

[V T]

Good book, I have it, and read it too. But table design is not the point of
dicussion here, is it ?

Visio as a product is not consistent with SQL Server it models. What can be
created directly in db, can be imported into visio but cannot be created or
modified in visio. This should be 2 way highway. At least for everything
else it is.