4

I've been googling for a long time now, reading lots of Normalization Tutorials, but haven't found an answer to my question....

The problem (in this question) is when I have something like a trip, where it has a start location and end location, but those locations are stored on a table and assigned an ID.

If I follow the normalization tutorials I end up with:

UNF

TripID, VehicleId, EndLocId, StartLocId, StartLocAddress, EndLocAddress

So if I normalize, I end up with these tables

1NF = UNF (no repeating groups)

2NF = 1NF (no partial dependency)

3NF (Separate Transitional Dependency)

Trip(TripId, VehicleId, StartLocId, EndLocId)
TripStartLoc(StartLocId, StartAddress)
TripEndLoc(EndLocId, EndLocAddress)

In my 3NF the last 2 tables will be exactly the same, so I want to use only one, a Location(LocId, LocAddress)

Trip(TripId, VehicleId, StartLocId, EndLocId)
Location(LocId, LocAddress)

But I can't find any step that allows me to do this, I know I can do it because there's no point in maintaining 2 tables with repeated information... but how to justify? or is this step outside of Normalization? I just want 2 foreign keys in my trip table pointing to the location table, one for the starting point and one to the end point.

Thank you for your help

Improve this question
3
  • 2
    Your schema with only two tables is correct. Normalization is a formal process, which should be done first by finding all the functional and multivalued depedencies in a set of attributes, and then applying an algorithm to find a normal form (see any good book on databases). The alternative approach is to design the database in term of an Entity-Relationship (or UML, or conceptual) data model and then transform it in a relational schema. All other “practical” approaches are simply not suitable for a good design, causing only doubt and confusion, even in very simple cases like in your example.
    – Renzo
    Commented May 10, 2016 at 10:16
  • 1
    Yes, till real application domains are considered, nothing tells formal normalization algorithm that attributes A and B are essentially the same. You probably will have also Vehicle(VehicleId,VehicleName) table which has the same data types, (int, varchar) for example, but definitley has other domain.
    – Serg
    Commented May 10, 2016 at 10:27
  • Thank you guys! From your comments I get now that this belongs somewhere outside the normalization scope, so it just depends on where I want to get, only the normalization, or good relations in my tables
    – figus
    Commented May 11, 2016 at 10:15

2 Answers 2

Reset to default
1

The purpose of normalization is to remove insert, update and delete anomalies from the data model. For instance, if you have a spelling error for the name of a location or say the lat-long is incorrect, you would want a single place to go to change that information. Your solution with two tables seems correct to me. Each column is dependent on the key, the whole key, and nothing but the key of the table.

Improve this answer
1
  • Awesome! your first sentence is what I've been missing all this time! Normalization is just to avoid those anomalies! so figuring out that those 2 location tables are the same, is up to the designer, thanks!
    – figus
    Commented May 11, 2016 at 10:18
0

You can add an extra field in Location table to differentiate start and end locations: 

Trip(TripId, VehicleId, StartLocId, EndLocId)
Location(LocId, LocAddress, LocType)

The extra field LocType should be of type boolean which will have one of two values (0,1).
You can use 0 for start location and 1 for end location.

Improve this answer
3
  • Thank you! but I don't think that LocType is necessary, I'm just treating the Location table as a catalog of locations, so it doesn't really matter if it is used as start or end point, that will depend if LocId is in StartLocId or in EndLocId
    – figus
    Commented May 11, 2016 at 10:17
  • Why would you do that? That then means you'd need duplicate locations in that table if you did a round-trip.
    – Phil Sumner
    Commented May 11, 2016 at 12:00
  • why?, if it is a round trip, the Location id would be the same in both, StartLocId and EndLocId, no need to duplicate, both ids would reference the same location (that's my reasoning)
    – figus
    Commented May 13, 2016 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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