CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
lomas09
lomas09

Reputation: 1104

Structure Databases for Unknown Columns

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

Upvotes: 1

Views: 59

Answers (2)

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

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.

enter image description here

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.

Upvotes: 0

Neria Nachum
Neria Nachum

Reputation: 1529

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';

Upvotes: 1

Related Questions