Structure Databases for Unknown Columns

Question

I am structuring a set of databases which all interconnect and contain information about each other. One of the main functions of the databases is to record paths and endpoints, for example;

Path 1 crosses endpoints point_1, point_2, point_3, point_4 and point_5
Path_1 = p1-p2-p3-p4-p5
Path 2 crosses endpoints point_1, point_2 and point_5
Path_1 = p1-p2-p5

Database Structure:

path_id | start_point | point_1 | point_2 | point_3 | end_point |
--------|-------------|---------|---------|---------|-----------|
    1   |     p_1     |   p_2   |   p_3   |   p_4   |    p_5    |
    2   |     p_1     |   p_2   |         |         |    p_5    |

And so on...

My problem is that I won't always know how many points it's going to cross. It could be 5 or it could be more than 15. How do I structure a database so that I could allocate a variable amount of points? (Path order is important)

To add to this, each point-point path also have an id associated with it. For example p1-p2 = 0001 and p2-p3 = 0002

Also how would I structure an SQL statement? At some point I want to query all of the paths that cross specific points, and if I have too many points I'm afraid I don't know how to create a statement so long.

This is probably a wrong database setup for this needs, any other suggestions are welcomed

score 1 · Answer 1 · answered Oct 20 '15 at 17:53

1

Columns represent the metadata and your points are actually data. A better design will possibly be:

path_id | point 
--------|-------------
    1   |  p_1     
    1   |  p_2     
    1   |  p_3     
    1   |  p_4  
    1   |  p_5     
    2   |  p_1  
    2   |  p_2     
    2   |  p_5

Of course you should consider adding columns for additional information (ie. 'order', if relevant), and a UK for these two columns combined.

A SELECT statement will be SELECT * FROM table WHERE point = 'p_1';

answered Oct 20 '15 at 17:53

Neria Nachum

1,519
1
20
37

that is not enough OP need to keep order of points in the path (I guess), so you can add 2 other columns like `id` and `nextId` – Alex Oct 20 '15 at 17:57
You are correct, though it wasn't stated that this is necessary. If it is, I suggested to add an 'order' column (reset for each path_id). – Neria Nachum Oct 20 '15 at 17:58
@Alex Point order is important since a path from p_5 to p_3 is not the same as p_3 to p_5 – lomas09 Oct 20 '15 at 20:32

score 0 · Answer 2 · edited May 23 '17 at 12:03

Using RDBMS for hierarchical data structure (specially infinite loops) is not recommended, its why graph database have been created.
Neo4j will serve your sample in an elegant approach.

For a relational solution you may use path enumeration pattern.
A UNIX path like /usr/local/lib/ is a path enumeration of the file system, where usr is the parent of local, which in turn is the parent of lib.
You can create a table containing paths graph(item hierarchy), calling it EnumPath.

I want to query all of the paths that cross specific point (like Pn)

SELECT EnumPath.PathName from EnumPath
WHERE EnumPath.Path LIKE '%Pn%'

Paths starting from Pn:

SELECT EnumPath.PathName from EnumPath
WHERE EnumPath.Path LIKE 'Pn%'

Paths ending to Pn:

SELECT EnumPath.PathName from EnumPath
WHERE EnumPath.Path LIKE '%Pn'

I want to query all of the paths that cross specific points (like Pk and Pi ...)

SELECT EnumPath.PathName from EnumPath
WHERE EnumPath.Path LIKE '%Pk%'
AND EnumPath.Path LIKE '%Pi%'
AND ...

A more comprehensive answer related to this question may be found here.

Structure Databases for Unknown Columns

2 Answers2