How to save an ordered table using Core Data (or SQL) with insert / delete?

Question

This question is in the context of Core Data, but if I'm not mistaken, it applies equally to the more general case of SQL.

I want to save an ordered table using Core Data, with the ability for the user:

• reorder lines
• add new lines anywhere
• delete existing row

What is the best data model for this? I see two ways:

1) Model it as an array: I add an int position property to my entity

2) Compare it as a linked list: I add two one-to-one relationships, next and previous from my object to myself

1) simplifies sorting, but it is painful to insert or delete, because you need to update the position all objects that appear after

2) makes it easy to insert or delete, but it is very difficult to sort. Actually, I don’t think I know how to express a sort descriptor (SQL ORDER BY ) for this case.

Now I can present an option on 1):

3) add an int ordering property for the entity, but instead of counting it one by one, specify a score of 100 per 100 (for example). Then inserting is as simple as finding any number between the order of the previous and next existing objects. Expensive numbering should only happen after filling 100 holes. Creating this float instead of int makes it even better: you can almost always find a new float halfway between two floats.

Am I on the right track with solution 3), or is there something smarter?

Answer 1

With iOS 5, you can (and should) use the NSOrderedSet and its mutable subclass. → Master Data Release Notes for OS X v10.7 and iOS 5.0

See the accepted answer in How to save an ordered list in Core Data .

Answer 2

If the order is arbitrary, that is, it is not integral to the modeled data, then you have no choice but to add an attribute or relation to maintain order.

I would recommend a linked list, as it is easiest to maintain. I’m not sure what you mean by a linked list, which is difficult to sort, since you most likely will not sort in any order. Instead, you just grab the topmost instance and go your way down.

Ordering on the divisible float attribute is a good idea. You can create an almost infinite number of intermediate indexes by simply subtracting the lower existing index from the higher existing index, dividing the result by two, and then adding this result to the lower index.

You can also combine a divisible index with a linked list if you need an order for tables, etc. A linked list makes it easier to find existing indexes, and a divisible index makes sorting easier if you need to.

Core Data resists this order because it is usually not needed. You do not want to add something to the data model if you do not want to simulate a real-world object, event, or condition that describes the model. Usually ordering / sorting is not specific to the model, but simply necessary for the UI / view. In this case, you should have sorting logic in the controller between the model and the view.

Think carefully before adding an order for modeling when you might not need it.