Mysql – Big lists in a relational DB

database-designMySQL

I need to store a list of items and that list belongs to a single user. The lists are going to be huge, so this has to be done as efficiently as possible. The lists also have to be modifiable to a small degree (able to add new items).

This is my (possible) solution:
Have a table items, where we store the data of our items. Table lists contains info about our lists (name, user_id etc). Table lists_lengths contains our lists lengths, e.g:

list_id | item_id_start | item_id_end
------------------------------------
1       | 1             | 200
2       | 201           | 220
1       | 221           | 300

If the lists get a lot of modifications, this might end up being a poor design though. Is this good or is the blatant list_id in the items table just the way to go?

Best Answer

Once the table gets really big, you will find that looking up an item to find out what list it is in becomes painfully slow.

I discuss that, plus provide efficient code, in my blog on ip-ranges. Your use case (non-overlapping ranges of items "owned" by various "lists") seems to be the same problem.