Entity Relationship diagram - Path and Node entities

  • boolean

    Valued Member

    Points: 63

    I have two tables; a path table that contains distinct paths and a node table that contains distinct nodes. A node can be present in one or more paths. A path is composed of one or more nodes (the path is composed of the id of the nodes concatenated by a dot). The image below shows an example of these tables:

    Capture

    I want to create the entity-relationship diagram, but I am confused about what to do in cardinalities.

    Is the diagram below correct?

    Diagramme entité-association vierge

    Thank you in advance

  • Jeff Moden

    SSC Guru

    Points: 996830

    Heh... Ok... I'll bite.  Is it? 😉

    I also wonder why people do this type of thing to perfectly good relational data.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • boolean

    Valued Member

    Points: 63

    I do this in order to store an XML path

  • Jonathan AC Roberts

    SSCoach

    Points: 17319

    The usual way to model this structure is like this:

    IF OBJECT_ID('Path','U') IS NOT NULL DROP TABLE Path
    IF OBJECT_ID('Node','U') IS NOT NULL DROP TABLE Node
    GO
    CREATE TABLE Node
    (
    NodeId int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
    Name varchar(20)
    );
    GO
    CREATE TABLE Path
    (
    PathNumber int NOT NULL,
    NodePosition int NOT NULL,
    NodeId int NOT NULL,
    CONSTRAINT PK_Path PRIMARY KEY CLUSTERED (PathNumber, NodePosition),
    CONSTRAINT FK_Path_Node FOREIGN KEY (NodeId) REFERENCES Node (NodeId)
    )
    GO
    INSERT INTO Node(Name)
    VALUES ('books'),
    ('book'),
    ('author'),
    ('title')
    GO
    INSERT INTO Path(PathNumber,NodePosition,NodeId)
    VALUES (1, 1, 1),
    (2, 1, 1),
    (2, 2, 2),
    (3, 1, 1),
    (3, 2, 2),
    (3, 3, 3),
    (4, 1, 1),
    (4, 2, 2),
    (4, 3, 3),
    (4, 4, 4)

    SELECT p.PathNumber,
    p.NodeId
    FROM Path p
    INNER JOIN Node n
    ON n.NodeId = p.NodeId
    ORDER BY p.PathNumber, p.NodePosition

    See the foreign key for how to get entity relationship.

     

  • Jeff Moden

    SSC Guru

    Points: 996830

    boolean wrote:

    I do this in order to store an XML path

    To be honest, that sounds like a slow and possibly error prone process being made to be slower and more resource intensive.

    But, before I go into any of that, I have a couple of questions...

    1. How are the values in the Path column of the Path table created?
    2. How big will the Path table ever get?
    3. Why are you trying to do this with a hierarchy of data within the Node table?  I ask that because it looks like it would be much better to create relational entities for each item in the Nodes table and work them using "bridging" or "mapping" tables instead so that you don't end up facing a recursive nightmare every time you want to do something with the data.
    4. If you  actually need this to be hierarchical, consider Nested Sets, which will eliminate the need for bridge tables and won't result in having half of a Cartesian Product stored in such a table.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply