Table Design Problem- 2:1 field relationship?!

[Hookstrat]

I am designing a database to track sewer maintenance. I need to give each specific structure within the system an intuitive ID tag. I wanted to use all integers instead of strings. First, the system is divided into 5 branches (ID #1,#2,#3,etc.). Then about every 6 feet there is a sewer main, I am thinking about using 1-01,1-02,1-03,1-04,..etc; the first 1 designates that the main is on the first branch and I used the zero because there could be up to 99 mains per branch (should I even use dashes??) Next, I need to identify the actual piece of line between each main. This is where my problem arises. For instance, in between mains 1-01 and 1-02 there is a piece of line which I could call 1-0102. However, in between mains 1-02 and 1-03 there is a line which I called 1-0203. In one table I have to put two lines for every main. So in the row with main 1-02 will be line 1-0102 AND line 1-0203. If I set up a seperate table trying to define each line seperately, then I have to put either two main ID's in one field or create two seperate fields (main ID 1, main ID 2) and then access will not let me relate both of them back to the Main IDs in the original table. How do I set up this/these table(s) and relationships so that as the system gets more and more complex I can still create things such as data access pages that will allow users to find specific structures and their related records? I know this may be too complex of a question to be answered here, but I really appreciate any input/advice. I've been racking my brain, searching helps sites, and using every resource possible but I can't come up with a viable solution. Thanks to anyone in advance for their help.
Most appreciatively,
Luke Hoekstra

 

[DonQuichote]

I think you have something like:

tblNode
  lngNodeId (autoincrement)
  strTag (string)
  ...

and

tblConnection
  lngConnectionId (autoincrement)
  strTag (string)
  lngStartNode (points to a node)
  lngEndNode (points to another node)
  ...

Make sure that the default values of lngStartNode and lngEndNode are removed (they default to zero).

You can now make the relations: add the node table TWICE to the relations window and make single relations to both the node tables

 

[cmmrfrds]

First, you know there is a problem with your relational design when a key starts to have multiple meaning - get the key down to a single meaning. The key in all tables is an integer that has no intrinsic meaning. This may or may not be the logical key for the data - sometimes it is. Create a model of you data by identifying entities(tables) and then identify the columns and the key. The columns in any table depend on the key the whole key and nothing but the key so help be Codd. It looks like some of your tables are Branch, Main, and Line - maybe Segments or Connectors. The tables you need are driven by the application needs. After the individual entities are defined then start looking at the relationships between entities which will probably require additional tables to identify individual units of data.

Are you trying to identify the area between 2 connections? What is the smallest unit that will be maintained? Does a connection have properties that are not part of the line or main? Answer all the questions using the model not after you start coding.

Ask questions about the relationship between entities. How does Main relate to Line? Can a Line be connected to multiple Mains? Can a Line be connected to multiple other Lines. Can a Main cross multiple branches? At this point, you may discover that additional tables are needed.

Resolve any many to many relationship between entities down to a one to many relationship. This will require a new table. I would think a Main has many Lines, but can a Line have more than one Main? There is probabaly a entity called MainLine which contains the primary key's (called foreign keys in this table) from Main and Line. The combination of these two columns is the logical key for the MainLine table. Make another integer column as the primary key for the table, which will make this table easier to maintain in the long run. Once you finish the design, you will not have keys that look like 01-01, 02-0102-01, etc.. At this point ask the question, is the MainLine table sufficient to capture all the properties of the connection? If not, you may need another table to identify the properties of the connection.

Once all the tables and relationships are identified and accounted for, then the application should have no problem growing or adding future requirements. On the other side, if you don't account for all the entities and relationships then there will be problems in the application. This can and should all be done in the logical model before any code is written.

Hope this helps.

 

[Hookstrat]

Thank you DonQuichote and cmmrfrds for the help...although I'm not sure if I understand either of your solutions to my problem?! I am trying to identify a unit of sewer pipe in between 2 connections(mains). I wanted to identify the section of pipe in between every 2 mains. So yes cmmrfrds, a line almost by definition will ALWAYS be connected to two mains. I think that I will have to settle for just identifying a line by the first or last main it is connected to, instead of trying to identify it by both of the mains it is connected to. Thanks again for the help.
Luke Hoekstra