First, apologies if this has been asked before. I don't really know how/what to search for, and am struggling to just put this question into words.
I'm trying to model aerial utilities in a PostGIS database. I'm getting stuck on how to model what would seem to be a line following sections of multiple other lines and how to represent that.
Most fiber optic cable needs support, and so metal wire rope ("strand") is attached to the poles and then the fiber is attached to that. Other fiber optic cable is designed to support it's own weight and can be directly attached to poles.
Here is a (not to scale!) map that tries to show some of the "test cases" that I need to be able to model:
One of the use cases we want to be able to support is calculating a location on the map when someone wants to find "X meters from the end of fiber cable Y" as some diagnostic/testing equipment locates faults that way. Fiber cables do have footage (or "meterage" - is that a word) printed on them, so that is data that can be collected.
I've tried to figure this out on my own, and here is a screenshot of what I have as a Database schema so far:
The most basic location related piece seems to be a utility pole, with a location and a few other columns (owner, label/tag, etc).
So normally, a length or piece of metal wire rope is attached to multiple poles, at a (different) height on each pole. So I have a table for pieces of metal wire rope (mostly just an ID column). And I have a table for "strand attachments" that should link that strand to a pole (with a column for the attachment height). I suspect that my strand_attachement table also should have a integer or real to sort the strand attachments for a piece of strand into an order that can be shown as a line (using the location points gathered from the poles).
The place I'm running into trouble is that I have a table with fiber cables. Those cables will follow the stand support lines, BUT they don't necessarily stay attached to only one strand line NOR do they necessarily follow the entire path of a piece of strand line. How should I model & record this in my database? I would assume part of the answer is another many to many table linking strand lines and fiber cables, but how do I represent and store what sections of the strand lines the fiber cable follows? When a cable follows multiple strand lines in its path, how do I record what order it follows the strand line "sections" in (maybe a column representing meters along the fiber cable where it meets the route of the strand section?)
I'm not new to database design, just GIS database design. I'm familiar with foreign keys, many to many vs many to one, etc.