DB Normalization ends up with 2 similar tables

normalization

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

Best Answer

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.

Related Question