Schema design for storing item combination with order

Question

What I need:

• Three types of items, and user can add/delete items.
• Use will select some items from those three packs, and arrange an order according to his/her preference.
• The number of records is not big (generally below 20).
• The tools I'm using are python, SQLAlchemy, and MySQL. (This might not be so important, anyway)

And this is my initial design idea:

• One table is enough for those three types of items as they share the same fields and only need to use a 'item_type' field to differentiate. Let's call the table 'Item'.
• Use another table called 'Preference' to save user's preference/priority on selected item. Cascade delete is set, so when user delete a row on Item table, it will also disappear from Preference table.

---

Example:

Use has added 5 items, and selected 4 of them into 'Preference'

[Item]  
id  item_name  item_type  preference_id (foreign key reference on Preference.id)
1       xxx         A            1
2       ooo         B            2 
3       aaa         A           NULL
4       bbb         C            3
5       ccc         C            4

[Preference]
id   priority
1      1 
2      2
3      4
4      3

Now the sorting is ['xxx','ooo','ccc','bbb'], say the user delete 'ooo' from Item table, the status would become:

[Item]  
id  item_name  item_type  preference_id
1       xxx         A            1
3       aaa         A           NULL
4       bbb         C            3
5       ccc         C            4

[Preference]
id   priority
1      1 
3      4
4      3

and the sorting become ['xxx','ccc','bbb'] now.

It looks simple when delete or add new items to the Preference. However, if I need to put an item in the middle of sorting, I might need to re-update the priority field for many rows. For example, make 'aaa' become the top priority would require update the priority column of all other existing 3 items in Preference table, the design doesn't loo very elegant for this scenario.

I would be appreciated if anyone has better ideas, or notice some other flaws on this design. Thanks!

Answer 1

The design doesn't seem awful, but I think you could solve your update issue by changing how you are assigning a priority number. Instead of using 1, 2, 3, 4, ..., you could use a higher range between the values, such as 10, 20, 30, 40, .... You might find you need an even larger set like 50, 100, 150, 200, ....

By increasing the range, you have some flexibility when a user changes the ordering of a given item. Instead of having to update all of the records, you could now use an available number between the existing two and save yourself the headache. For example:

AAA -> 10
BBB -> 20
CCC -> 30

We want to move CCC to be the second item (in between AAA and BBB), so we change 30 to 15. So now:

AAA -> 10
CCC -> 15
BBB -> 20

Of course, you'll still have to re-assign all the priorities if the user changes too many of the items, as you'll eventually run out of empty numbers. When you're doing your selection, just remember to order by the priority.