Normalizing a database column

Question

The objective here is to be able to query a database providing it with a Journey, based on the Journey the database will return all of the Stop Codes that run through this journey.

So for example I need to be able to say, "Select all of the stop codes that run through journey 34". This should then only return STOP CODE: SZDASDASDE. (in production many more codes will be returned).

Above you can see an image of the first table in a database.

You can also see the second table where each STOP CODE has many JOURNEYS as parents. As far as I know putting multiple journeys into a single field does not follow standard database design so if anyone can help me fix that I would really appreciate it.

These images were taken in Microsoft's Excel just to plan how I'm going to do this, production will be using a MySQL database.

Thanks

Answer 1

You have a many to many relationship between Stop Codes and Journeys. To eradicate this, you need to decompose the relationship.

In order to do this, you need an intermediary table, let's call it JourneyStopCode, which will look like:

JourneyStopCode:
JourneyStopCodeID (primarykey)
JourneyID
StopCodeID

Then your Stop Code table wouldn't have a JourneyID field.

To retrieve stop codes for a journey, you'd do:

SELECT * FROM StopCode
INNER JOIN JourneyStopCode ON StopCode.StopCodeID = JourneyStopCode.StopCodeID
INNER JOIN Journey On Journey.JourneyID = JourneyStopCode.JourneyID
WHERE JourneyID = @yourJourneyID

Edit: To visualise:

---------------        ---------------------         ----------------
|  Journey    |        | JourneyStopCode   |         |  StopCode    |
---------------        ---------------------         ----------------
| JourneyID   |<---    | JourneyStopCodeID |     --->|  StopCodeID  |
| Description |   |----| JourneyID         |     |   |  Latitude    |
---------------        | StopCodeID        |------   |  Longitude   |
                       ---------------------         ----------------

Then your data would look like:

Journey

----------------------------------------
| JourneyID | Description              | 
----------------------------------------
| 34        | Southampton - Portsmouth |
----------------------------------------

StopCode

----------------------------------------
| StopID | Latitude | Longitude        | 
----------------------------------------
| SSDAFS | 12345    | 67890            |
----------------------------------------

JourneyStopCode

------------------------------------------
| JourneyStopID | JourneyID | StopCodeID | 
------------------------------------------
| 1             | 34        | SSDAFS     |
------------------------------------------

Answer 2

Journeys to stop codes is a many to many relation ship, you want a join table most likely.

Table 1 :
SID   |   Stop Code   |  Long    |   lat 
0     | ASDFSAFA      | 1        | 2
1     | sdDSGSDGS     | 4        | 0 
....

Table 2 : 
Journey   | Description 
0         | Blah blah blah


Table 3 : 
Journey  | SID 
0        |  1
2        |  1
1        |  4


SELECT A.Longitude, A.Latitude FROM TABLE1 WHERE A.SID IN (
    SELECT SID FROM TABLE3 WHERE JOURNEY = 0
);