What are criticisms against using "display_order" fields in your database design?

Question

In my company's software product, a number of database tables make use of a "display_order" field. It's an integer that controls the display order of the elements on the page.

Deep down in my gut, I feel like this is a bad practice. (Or at least, a less-than-optimal design choice). However, I'm having trouble articulating to myself why this is a bad practice.

So far, the only reasons I've been able to come up with are these:

• It mixes the view into the model.

• Changing order is a potentially expensive database operation that touches many different rows.

• In many cases, if you knew the order of the elements, the table might be small enough that the table is a waste to begin with. For example, in our "statuses" table, the statuses ought to be a simple hard-coded array instead of a separate "lookup" table.

Does anyone else have any good arguments for avoiding "display_order" fields in your database design?

Answer 1

In some cases, this is the best option for ordering items.

In particular - when items do not have a natural ordering, or the natural ordering is not something that can easily be done by a program (say natural language - phonemes).

The converse is also true and would be the "meat" of an argument against such fields - if the items do have a natural order that can be easily implemented (numeric, alpha-bet), then such fields do not have a place.

This discussion ignores any requirement of the users to set sorting of items - if such a requirement exists, a "display_order" field is the way to go.

Answer 2

I think it's a tough one, but good that you're aware of it. The only argument I know against is that it blurs the logical distinctions between the roles of your system's components.

However, I find that in a lot of projects, data is returned based on the order of insert (due to auto-incrementing primary keys and scripts that run inserts based on alphabetical / numeric ordering of source data) and sites / other software uses this ordering as an implicit assumption. It's only when you do updates to the data that disrupts the default ordering that people say 'looks like I need display ordering here in the database'

Answer 3

In the majority of implementations where I have used a "Display Order" it has related to navigation / subnavigation in fly-outs. In this case, since the client usually did not have the expertise or need to ever touch the code or database, using display-order along with drag-n-drop proved to be the most efficient design. There are often times when a client will want to display items on a page without a particularly intuitive order to them, or choose to display certain page elements in a interchangable order that they can easliy manipulate and display_order is designed for just this task, so using it makes sense.

Answer 4

In many cases, if you knew the order of the elements, the table might be small enough that the table is a waste to begin with. For example, in our "statuses" table, the statuses ought to be a simple hard-coded array instead of a separate "lookup" table.

As a database guy, I'd have to disagree with this. The odds are good that your statuses are involved in some kind of integrity constraint. When that's the case, putting them in a table makes maintenance (usually adding "just one more" status) a simple matter of inserting a row.

You could also implement that kind of thing as a CHECK constraint, but changing it (adding "just one more" status) would mean altering the table, which in turn usually means a round-trip through QA and testing. Adding a row to a table usually doesn't require QA and regression testing.

But you can't implement statuses that take part in an integrity constraint by hard-coding an array in application code. Application code simply isn't in a position to enforce a constraint (or a view ordering) that's shared among all applications and all users. Only the dbms can do that.