Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have some JSON data in this format (5,000 instances for now):

{"id":"123456","icon":"icon.png","caseName":"name of case"}

I am allowing the user to search for the name of the case and then return the id and icon to them, and in some situations, use the case id to run other functions, based on the search.

How should I read this data? I'm currently leaning towards a switch because I want the switch to allow me to define multiple cases giving me the same result:

name of case and name-of-case should return me the id 123456

I can easily use a switch based on the string, listing out different cases, to do this. Probably going to be extremely tedious, but it's an option.

So, my options I can think of are as follows:

  1. Run a massive switch based on the query string (probably in a separate PHP file)
  2. Store this result in a MySQL database, then based on the query string, retrieve the data, and I probably need to do indexing (which I understand, but have no idea how to implement).

Also, I would like to prepare for the scenario that this 5,000 instances increases to 10,000 instances at some point in time.

What's the best way of going about this?

Thanks.

share|improve this question
1  
I don't understand “the name of case and name-of-case should return me the id 123456”. Does it mean that caseNames uniquely identify the entities, but ids are not? If so, what's the point of using the ids? –  MainMa Feb 12 at 7:40
    
@MainMa - No, IDs uniquely identify the cases, what I meant is to allow alternative ways of passing the query. If I search for "name of case" or "name-of-case" I receive the ID 123456. Doing this with a switch statement is easy by just stacking the different case matches above one another. –  user3201185 Feb 12 at 7:42
2  
Am I understanding you correctly that you want to dynamically build a php script with a very big switch-tree? Aren't you then basically making your own (very imperfect) database engine? I'd say use a database, your problems have been solved by others, no need to re-solve them. –  Pieter B Feb 12 at 8:12
    
@PieterB - Now that you put it like that, yeah, it makes sense to use a normal database. Thanks for the enlightenment. –  user3201185 Feb 12 at 8:15
3  
You could always use SQLite if you wanted to avoid having a separate DB server. –  Rory Hunter Feb 12 at 11:48

3 Answers 3

up vote 7 down vote accepted

Always use SQL. The amount effort to read the data from SQL is in your case of complexity similar with a query. So why bother to add extra code?

share|improve this answer
3  
It'd be a lot more flexible too, making more complex searches easier to do. –  GrandmasterB Feb 12 at 8:05
    
But would the data retrieval be quick? I know that it's dependent on 1001 other factors, but in general, an indexed database containing 10,000 elements, any idea how long a query may take to complete on average? –  user3201185 Feb 12 at 8:17
2  
It'll be very fast. Honestly, even unindexed it'll likely be faster than manually doing the search in your code. –  GrandmasterB Feb 12 at 8:20
4  
@user3201185: In the grand scheme of things, 10,000 is not very many items at all. Databases are built to handle much much higher loads while maintaining high performance. –  Phoshi Feb 12 at 9:11
2  
@user3201185 it'll probably be measured in milliseconds, and most of that will be network time for such a simple search. –  GrandmasterB Feb 12 at 16:05

Listing 10000 cases manually is a titanic work. You should use a database.

If more than one spelling is possible, you can use an Alternatives table, to fetch the canonical version of a case name.

share|improve this answer
    
Thanks, now I know where to begin, how does it work though? I mean if I have this other table of data, that means that I have 2 instead of just 1 storage of all this data, and if I have more than 2 ways of spelling it, more tables? Oh and what do you mean by "fetch the canonical version of a case name"? –  user3201185 Feb 12 at 8:27
1  
I am sorry I do not understand your question.. what "other table of data" ? you can combine queries with multiple tables, make joins and what not... and even with 1 milion records will still be fast. –  Adrian Pirvulescu Feb 12 at 9:16
    
@AdrianPirvulescu - Alright, noted, thanks. I'll have to read up more about it. –  user3201185 Feb 12 at 9:28

If you take pride in your work, then:

  • the overhead of doing an database query every single time should disgust you
  • the idea of maintaining a massive hard-coded switch should horrify you

Most languages have support for collections and include things like hash maps, and this is likely to be very well optimised (and for languages that don't it's relatively trivial to implement anyway). This is what you should be using. For a collection of 10000 items it's extremely likely that you can find a value from your key faster than you can send a "do nothing" request to a database and receive a "nothing done" reply back.

The next question is, when you're creating the collection where does the data come from? This depends on the nature of your software - does it start once and handle many requests (like virtually everything that is sane), or is your software started from scratch every time (e.g. like the old CGI interface web servers used to use back when "internet connection" meant a dial-up modem; where every single "page fetch" would cause a massive amount of bloat starting a script interpreter, finding the script, letting the script initialise itself, etc).

For the first case you can do whatever is easiest for you to maintain. This could be loading the data for your collection from a "plain text" file or from the database. Performance doesn't really matter here because it only happens when your software is first started (which might be once every 200 days or something).

For the second case; you should ignore everything I've said above and do a database lookup every single time. Even though this should disgust you, anything you could do in your own software (starting/loading/initialising everything from scratch every single time) should disgust you even more.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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