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!