1

I'm trying to design an electronic toll collection system, where I have stumbled on decision how to design road section. Each road section is designed in a way that it has references to other road sections that lie ahead(RSA) , behind(RSB) and across(ORS). I tried to visually demonstrate them in figure below.

Visual presentation of Road Section with their primary keys is given below.

   |     |    || Road     |
   |          || Section  |
   |     |    || Ahead    |
   |    (1)   ||   (6)    |
------------------------------
   |     |    ||    |     |   
   |          ||          |   
   | Opposite || Road     |    
   | Road     || Section  |
   | Section  ||          |
   |     |    ||    |     |
   |    (2)   ||   (5)    |   
---------|----------|---------
   |          ||          |
   |     |    || Road     |
   |    (3)   || Section  | 
   |     |    || Behind   |
   |     |    ||   (4)    |

I have two approaches in mind in designing such entity. The first one is using same table and have 3 columns referencing other rows in the same table.

Road Section Table
---------------------------------------------------
| PK_RS | FK_ORS | FK_RSA | FK_RSB | Other data 
---------------------------------------------------
|   5   |   2    |   6    |   4    |  ...
|   4   |   3    |   5    |  null  |  ...

The second one is to create separate table for road section relationships, called Road Section Mapping

Road Section Table
-----------------------------
| PK_RS | Other data 
-----------------------------
|   4   |  ...
|   5   |  ...
Road Section Map Table
------------------------------------
| PK_RS | FK_ORS | FK_RSA | FK_RSB |
------------------------------------
|   5   |   2    |   6    |   4    |
|   4   |   3    |   5    |  null  | 

In the first case I will need to create rows with null references at first and update foreign keys later. In second a can create relations after creating road sections first.

What is the best practice in such kind of models?

  • May you please add more information to your question, I got confused about the details. thanks – Ahmad Abuhasna Nov 15 '16 at 12:10
-1

First approach seems to be more logical. Because ManyToOne usually stored in same table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.