Essentially, the second solution you propose is a linked list. linked list
implemented at the database level are usually not a good idea. To retrieve a
list of
nelements, you will need
ndatabase access (or use complicated
queries). Performance wise, retrieving a list in O(n) is awfully not
efficient.
In regular pre, linked list are used to get better insert performance
compared to arrays (no need to move all elements around). In your database,
updating all elements is not that complicated in only 2 queries :
UPDATe item.order = item.order + 1 FROM item WHERe order > 3INSERT INTO item (order, ...) VALUES (3, ...)
I remember seeing a reuseable app that implemented all that and a nice admin
interface, but I cant find it right now …
To summarize, definitly use solution #1 and stay away from solution #2 unless
you have a very very good reason not to !
