How to provide the ability to get a list of items and reorder them at your will?
Let me explain. Imagine you have a database table with categories. You want to provide them in different orders on the front-end. For example, you want to allow the administrators to reorder the order that the categories are shown in the navigation bar, but you also want to allow them to reorder how they will be shown in a dropdown jumplist. Not the best example, I know, but bear with me.
Right now, my idea for this example is:
The Categories Table (ID, NAME, DESCRIPTION)
An Order Items Table (ORDER_IDENTIFIER, ARG, ORDER)
And I'd have for example:
(ID, NAME, DESCRIPTION)
1 | Category 1 | Just a test category.
2 | Category 2 | With the ordering, this should be in first place
3 | Category 3 | This goes to the middle.
(ORDER_IDENTIFIER, ARG, ORDER)
tabs | 1 | 3
tabs | 2 | 1
tabs | 3 | 2
dropdown | 1 | 2
dropdown | 2 | 3
dropdown | 3 | 1
I hope the example is clear enough. Basically, I have 3 categories, but I have two ways to order them. I can order them for display as tabs in the navigation bar, or for the dropdown list in the website.
Now I use a JOIN in which when the ARG of the order is equal to the ID of the category and the ORDER_IDENTIFIER is equal to what I'm trying to get (for example, let's say I'm trying to get the tabs), and then when they're joined, I know the ORDER of that category.
What do you think of this idea is what I'm wondering. I know that I could just make a TAB_ORDER and a DROPDOWN_ORDER field in the Categories table, but I want to create an ordering system that is completely abstracted from whatever I'm trying to order. For example, in that case, all it would take would be creating three extra order items:
top3 | 1 | 1
top3 | 2 | 3
top3 | 3 | 2
And now I just get the categories' orders for the top3, and I have their new order. I don't know if I'm making myself clear.
So far, the big disadvantages of this method that I could find are:
-> Without a record in the order items table, I can't obtain the categories. If I add a Category 4, for example, without creating any order items for it, it won't show up when I get Category 4. I really have no idea how to sort this one out.
-> It's a JOIN statement, obviously. I was thinking that maybe a ORDERS field could be made which kept the orders for a certain category in an Array. (eg. serialize a PHP array and store it in an ORDERS field). But that would not only make the DB compatible only with PHP, but it would also mean serializing/unserializing the orders every time I want to get them. Or I could instead make my own format for the orders, but it would still not be good, because I'd be making it exclusive now to my application. So the orders table seemed to me like the best way to go about doing this.
What do you think?