This is a general question although i need to do it in Mysql but can be considered for any way of storage..
Say i have multiple ids id1, id2, id3,id4...
And for each pair there is a corresponding value or some secret value which needs to be stored in a table...
The values are symmetric ie. the values for (id1, id2) is same as (id2, id1).
Please suggest the table schema or any way to store it...One way obviously is that i have a table with three columns...
id, id, value
and store things like :
id1, id2, val1
id2, id1, val1
id1, id3, val3
id3, id1, val3
The issue i see with this is that duplicate info is being stored in the table...
Also the ids are strings and vals are also strings..
Other way i can think of is of two columsn
if id1 < id2 then i store
id1 + " " + id2 val1
ie. i concatenate them and keep the first one which is smallest ..
Please suggest alternate approaches and pros and cons of above...