DB Schema Quesion: Maintaining Item Order

Question

Greetings!

I have a database containing two tables; List and ListItem. These tables have a one-to-many relationship. But a requirement is to allow the ListItems related to a given List to be sorted. Aside from having a "sort order" field in the ListItem table, what would be a good approach to this?

Answer 1

If you're being asked to provide a method other than a Sort or Sequence field, how about a CreatedAt field? Sometimes a separate sort order table (ListItemSortOrder) is useful.

Answer 2

I see three alternatives:

• sort order field in the list item table. Normally the best choice: Simple and does what you need. If it is what you want, this could be automatically be filled with a timestamp.

• a separate table containing the sorting information. Useful if there is an arbitrary number of orderings to be maintained

• a next/prev column: This is a nightmare in most scenarios, but could actually be usefull, if you have huge amounts of list items and need to insert in the middle a lot.

Answer 3

If the domain-specific meaning of your tables is really as generic as "List" and "List Item", then the best you can do is to provide a generic "Sort Order" column.

But if your tables have more specific meaning in the business domain, you'll want the sort order column to have more meaning in that domain. An example would be Orders and LineItem tables, where the sort column would be LineItemNumber:

CREATE TABLE [Orders](
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY
)
GO

CREATE TABLE [LineItems](
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
 [OrderID] [int] NOT NULL,
 [LineItemNumber] [int] NOT NULL,
CONSTRAINT [UC_LineItems] UNIQUE NONCLUSTERED 
(
 [OrderID] ASC,
 [LineItemNumber] ASC
)
)
GO

ALTER TABLE [LineItems]  WITH CHECK ADD  CONSTRAINT [FK_LineItems_Orders]
    FOREIGN KEY([OrderID])
REFERENCES [Orders] ([ID])
GO
ALTER TABLE [LineItems] CHECK CONSTRAINT [FK_LineItems_Orders]
GO

Note that I added the domain-specific constraint that a given order can't have two line items with the same LineItemNumber. That constraint is not necessary in evey case of List/ListItem.