Search code examples
mysqlsqldatabase-designdatabase-normalization

Normalizing a database column

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).

http://i.imgur.com/9ZBSxmq.png

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

enter image description here

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

Solution

  • 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     |
------------------------------------------