I have following use case where there are bunch of IDs (GUID) which are mapped to each other. I need to lookup all the IDs given any single ID. For example, let's say my data has following columns:
uid, id1, id2, id3, id4, id5
How to support the following queries:
1. SELECT uid FROM table WHERE id1=x; 2. SELECT id1, id2, id3, id4 FROM table WHERE uid=xyz;
The number of IDs can change (I have to add new columns).
I can create indexes on all columns but that would not be very effective. Should I model this data differently? Is there any NoSQL database which can help model this use-case?
To give more business context for clarity, we have data coming from multiple data sources which have their own custom_id. But they all identify the same person (UID). Thats why we need to map all the data source ids to our own internal UID. Hence the question.
- The number of columns have max bound of 20-25.
- They are nullable.
- The number of id's can be different per row (thats why nullable).
- Datatypes are STRING for all.
- UID will be primary key.