What are criticisms against using "display_order" fields in your database design? [closed]
This question does not appear to be about programming within the scope defined in the help center.
Closed 4 years ago.
Improve this questionIn my company's software product, a number of database tables make use of开发者_JS百科 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?
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.
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'
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.
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.
精彩评论