Orm or Something to Handle SQL Tables with an Order Column Efficiently

What is the best way to store the order between rows in the database?

Make rowNumber a floating point number. When user inserts between rows x and y, the new row gets rowNumber = (x.rowNumber + y.rowNumer) / 2.

When you want to move a row, just update its rowNumber the same way based on target position.

New rows get rowNumber eg. MAX + 256.

Edit Microsoft solved this in SQL Server 2008 using new HierarchyId datatype. You can insert between 2 items almost as many times as you want - until you run out of HierarchyId max size.

How to save a particular, mutable order into a database

The "naive" approach you suggest is also the best practice!

What's the better database design: more tables or more columns?

I have a few fairly simple rules of thumb I follow when designing databases, which I think can be used to help make decisions like this....

1. Favor normalization. Denormalization is a form of optimization, with all the requisite tradeoffs, and as such it should be approached with a YAGNI attitude.
2. Make sure that client code referencing the database is decoupled enough from the schema that reworking it doesn't necessitate a major redesign of the client(s).
3. Don't be afraid to denormalize when it provides a clear benefit to performance or query complexity.
4. Use views or downstream tables to implement denormalization rather than denormalizing the core of the schema, when data volume and usage scenarios allow for it.

The usual result of these rules is that the initial design will favor tables over columns, with a focus on eliminating redundancy. As the project progresses and denormalization points are identified, the overall structure will evolve toward a balance that compromises with limited redundancy and column proliferation in exchange for other valuable benefits.

Versioning in SQL Tables - how to handle it?

I think you've started down the wrong path.

Typically, for versioning or storing historical data you do one of two (or both) things.

1. You have a separate table that mimics the original table + a date/time column for the date it was changed. Whenever a record is updated, you insert the existing contents into the history table just prior to the update.

2. You have a separate warehouse database. In this case you can either version it just like in #1 above OR you simply snapshot it once every so often (hourly, daily, weekly..)

Keeping your version number in the same table as your normal one has several problems. First, the table size is going to grow like crazy. This will put constant pressure on normal production queries.

Second, it's going to radically increase your query complexity for joins etc in order to make sure the latest version of each record is being used.

Using a sort order column in a database table

Update product set order = order+1 where order >= @value changed

Though over time you'll get larger and larger "spaces" in your order but it will still "sort"

This will add 1 to the value being changed and every value after it in one statement, but the above statement is still true. larger and larger "spaces" will form in your order possibly getting to the point of exceeding an INT value.

Alternate solution given desire for no spaces:

Imagine a procedure for: UpdateSortOrder with parameters of @NewOrderVal, @IDToChange,@OriginalOrderVal

Two step process depending if new/old order is moving up or down the sort.

If @NewOrderVal < @OriginalOrderVal --Moving down chain 

--Create space for the movement; no point in changing the original 
    Update product set order = order+1 
    where order BETWEEN @NewOrderVal and @OriginalOrderVal-1;

end if

If @NewOrderVal > @OriginalOrderVal --Moving up chain

--Create space  for the momvement; no point in changing the original  
  Update product set order = order-1 
  where order between @OriginalOrderVal+1 and @NewOrderVal
end if

--Finally update the one we moved to correct value

    update product set order = @newOrderVal where ID=@IDToChange;

Regarding best practice; most environments I've been in typically want something grouped by category and sorted alphabetically or based on "popularity on sale" thus negating the need to provide a user defined sort.

Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc

One reason that selecting specific columns is better is that it raises the probability that SQL Server can access the data from indexes rather than querying the table data.

Here's a post I wrote about it: The real reason select queries are bad index coverage

It's also less fragile to change, since any code that consumes the data will be getting the same data structure regardless of changes you make to the table schema in the future.

---

Related Topics