What is the best pattern for persisting entity order in a relational database?

Question

Environment: ASP.NET MVC application with EF (Database-first with control over the db design) and Sql Server.

I have a lot of entities that have user-generated properties. I need a way for the user to be able to specify the order of these elements.

Slightly more detail: The user can create a "template" that they can then add "properties" to. These "properties" need to be ordered. There is also >= 4 other different entity types that also require user-specified ordering.

I have no problem with the front-end aspect of this, but I want to know what is the best way to persist the element order in sql server.

The obvious solution to me is to give each entity a column "Order" (or another non-keyword name) and, upon a reordering (eg. moving element #4 to #2) update all affected entities.

Is this the best way to solve this problem?

Answer 1

This doesn't sound like a small project and you might have various other dynamic customizations on top of the SortOrder property.

Adding an SortOrder column to your entity table is certainly not a bad approach, but this approach might clog up your data with information that doesn't necessarily belong to that entity (especially if multiple users can customize the same instances).

So I've got an alternative idea for you:

• Add a CustomizationNode table (or something similar) to your database

Here you store SortOrder and potentially other kinds of metadata and user customizations which are not necessarily part of the conceptual entity.

Then, should you need to add/change/remove any customization info, you'll only need to do so in one table rather than several. And you don't need to migrate your entities whenever you change the customization capabilities.

Depending on your situation, you can link them in one of several ways:

1. Add a single column CustomizationNodeId to each entity table

This pertains to having a single customization per entity instance and is the simplest solution. Also one customization could be shared across multiple entities of the same type (or even different types, though that probably doesn't make much sense)

2. Add multiple columns EntityXId, EntityYId to the CustomizationNode table.

In principle, only one of these ID fields would be filled and the others will be empty. Can seem a bit "off", but is not necessarily a wrong way to do it.

While you lose the ability to share a customization across multiple entities of the same type, you gain the ability to have multiple customizations per entity and additional FK's such as the UserID. This would allow you to have a per-user customization.

3. Add a link table between each EntityX and CustomizationNode

This is the most complex but also the most versatile solution. It embodies adding a table with a FK to each table you wish to link.

One important benefit you gain is additional decoupling. Customizations and entities don't know of eachother's existence and change without impacting one another.

Furthermore you can add additional metadata to those link tables, such that you can have things like versioning on top of everything mentioned above in 1 and 2.

The bottom line is, if your application is highly dynamic and customizeable then you might want to store "metadata" separately from your actual "data".