0

I have a SQLite table named User. Each User has a primary UserID and multiple SecIDs (all integers). The SecIDs are stored in CSV format as a single field (type is String). For example, suppose User1 has SecIDs as 4,5,6,7 and User2 has SecIDs as 8,9,55,66.

So for User1 :-
UserID = 1
SecIDs = "4,5,6,7"
And for User2 :-
UserID = 2
SecIDs = "8,9,55,66" (And so on)

Now my question is- I have a particular SecID say 5. And I have to extract the UserID corresponding to that (which is 1). So what would be the Query for this operation?

P.S- There could be hundreds of thousands of User Records in my SQLite table. So the search query should be an optimized for minimum time consumption.

Improve this question

1 Answer 1

Reset to default
1

That's simply not how you do SQL or relational databases, so SQLite doesn't have a specific SELECT syntax for that. You can of course search all the field by a String patter ~"12", for example, but that is a terrible idea, because it matches 12 as much as 128 as much as 112.

What you should do is re-structure your database. Get a 1-to-n mapping table to map SecIDs to users; it's not hard at all, a lot faster, and but a two-column table:

+-----+----+
|SecID|User|
+-----+----+
|    4|   1|
|    5|   1|
|    6|   1|
|    7|   1|
|    8|   2|
|    9|   2|
|   55|   2|
|   66|   2|
+-----+----+

It will be much faster, less error-prone, and much more compact in both RAM and permanent storage.

Improve this answer
3
  • We have considered this option but the current scenario is like, we already have a working code, up and running wherein there's only one SecID for each UserID. Now there's a new requirement as per which a particular User can have multiple SecIDs. Since there's already a set of existing functions for (SecID-UserID) pair, so we are trying to accommodate this new requirement in our existing environment. Adding a new Table would mean having to add new methods to our program. So this doubt :)
    – satya prakash Panigrahi
    Commented Apr 16, 2015 at 10:07
  • You would still have to change your table since your string field is not an integer field, and then you'd have to change your software to handle that field. What you describe is what I'd call "a fundamentally bad design decision".
    – Marcus Müller
    Commented Apr 16, 2015 at 11:41
  • Completely agree with you on the "bad design" part :), anyway we have decided to go for a separate table as we are not able to figure out any other better option.
    – satya prakash Panigrahi
    Commented Apr 17, 2015 at 8:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.