Entity Relationship diagram - Path and Node entities
-
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:
I want to create the entity-relationship diagram, but I am confused about what to do in cardinalities.
Is the diagram below correct?
Thank you in advance
-
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I do this in order to store an XML path
-
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.NodePositionSee the foreign key for how to get entity relationship.
- 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...
- How are the values in the Path column of the Path table created?
- How big will the Path table ever get?
- 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.
- 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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply