mysql - How to store immutable, unique, ordered, lists? -

i have entity millions of instances. each instance has reference ordered list of items. lists have unique no list stored more once. once created, both lists , entity instances immutable. there far more entity instances lists, , database has support fast insertions of entities.

so what's insert-efficient, robust, way of storing immutable, unique, ordered lists?

---

edit: far, i've considered these approaches:

1)

lists table has these columns: <list_id> <order> <item> if list #5 contains elements [10,20,30] table contain:

5   1   10 5   2   20 5   3   30 

the entity table have item_list_id column references lists table (it's not foreign key since list_id not unique column in lists table - the can solved adding table single column contains valid list_ids ).

• this solution makes inserts bit tricky
• it places responsibility enforcing uniqueness of lists on application, isn't great.

2)

lists table has these columns: <list_id> <item1> <item2> <item3> ... <itemn> if list #5 contains elements [10,20,30] table contain:

5   10   20   30 

the entity table have item_list_id column references lists table.

• this solution less robust since list lengths limited (although isn't huge problem me since lists unlikely contain more 10 or 20 elements)
• this approach quite horrible query ("find lists in particular item appears" has specify each , every column), , nightmare map using orm.
• insertions of new entities not bad since lists typically 4-5 items long, can index first few columns.
• enforcement of uniqueness still in hands of application.

3)

use solution #1, replace meaningless list_id hash (say sha-1) on list in serialized form.

• this way uniqueness more strictly enforced since lists have unique hashes
• insertions simpler , quicker(?)
• the data integrity enforcement still in hands of application.

if want avoid having duplicate lists need use relational division inside trigger.

see https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

and

https://dba.stackexchange.com/questions/45829/what-is-the-name-of-this-type-of-query-and-what-is-an-efficient-example