CODEWITHSUNDEEP
database-designhibernate
j0ntech
j0ntech

Reputation: 1168

Storing the order of objects in a database

I have a pretty basic data model: a bunch of Routes and a bunch of Locations. They have a many-to-many relationship requiring a linking table (e.g. RouteLocation).

I'm having trouble with coming up with a way to store the order of the locations within a route. My first instinct would be to have an order column for the Route table which is basically a comma-separated string of the IDs of the locations in that route, ordered of course. This means I have to do the ordering within the webapp (parse the string, compare IDs and so on). I'd rather have it done by the database system (ORDER BY for example).

I could maybe store the index of a location within a route in the RouteLocation table, then I could have a query like SELECT *, RouteLocation.order FROM Location, RouteLocation WHERE Location.id IN (SELECT RouteLocation.locationId FROM RouteLocation WHERE RouteLocation.routeId = 1234) ORDER BY RouteLocation.order ASC;. My problem with this approach is that I'm using the Hibernate framework which just abstracts the linking table away. The query would be something like (I forget the exact Hibernate SQL dialect) SELECT loc from Location loc WHERE loc.route = :route.

Upvotes: 0

Views: 109

Answers (1)

Kilian Foth
Kilian Foth

Reputation: 14346

Storing a list of things as a comma-separated string is always bad, for various reasons (you can't tell how much space you might need for the string, iterating the list takes more processing effort every single time, changing it takes even more effort...). Duplicating foreign relations that already exist is doubly bad, because you introduce redundancy that serves no purpose except to make catastrophic inconsistency possible. And when the things you store are actually foreign keys, badness takes on a whole new level. As it is, I see only disadvantages for that solution and no advantages.

I don't understand what you say the issue is with the obvious solution (using the RouteLocation records to store not only the association of stops in a route but also their order), but it can't possibly be worse than rolling your own redundant n:m relation emulator. If the issue is that Hibernate transforms away data that the code needs to achieve correct behaviour, then clearly it is working against you and not for you; you should ask an implementation question on Stackoverflow, something like "How do I get Hibernate to store additional data in a linking table?".

Upvotes: 3

Related Questions