Hi I'm using CakePHP and I'm wondering if it's advisable to store things that don't change a lot in the database lik the list of cities?
+5
A:
If your application already needs a database, why would you keep data anywhere else?
If the list doesn't change (per installation) and it's reasonably small and frequently used, then it might be worth reading it once on initialization and caching the result to improve performance and reduce the load on the database.
richj
2010-05-01 12:01:28
+1
A:
You get all sorts of queries and retrievals out of the box, the same way you access any other of your data. Databases are as cheap as flat files today, but you get a full service.
sibidiba
2010-05-01 23:30:37
A:
If it is pretty much a static list, then you can store it either in the db or a file, but keep it in memory for use. In other words, load it once whether from db or file. What you don't want to do is keep taking a hit loading it. Especially if you use it on most page views. Those little bits of time add up if you have a large number of visitors.
The flip side, of course, is if you find yourself doing this for large lists or lots and lots of little lists. Then you could run into problems of keeping too much in memory.
Bill the Lizard is right about it being important whether or not the list links to other tables. If it does, then you will need it in the db if you need queries that will include it.
jeffa00
2010-05-01 23:36:51
Not accurate - see the datasource I linked in my answer. It will allow you to relate models to array data and run find queries or base models on static array data.
Abba Bryant
2010-05-05 23:29:12
Good point. I should have said if you need to query them IN THE DATABASE. If you don't need to do the relation in the database itself, then you are correct. I work mostly in .Net these days and your approach would be similar to Linq. It can relate data structures in memory with db queries too. In both cases, the relation is occuring at the app level, not in the database. That's great for many apps, but some need to have views, stored procs, etc. that do the relation at the DB level. Which way you go depends on specifics about the project.
jeffa00
2010-05-07 17:59:59
+1
A:
I see this question has had an answer accepted - I still want to chime in with my $0.02
The way I typically do for arrays of static data (country list, timezone list, immutable sets you would use enum for...) is to use this array datasource.
It allows you to map relationships between db models and array based models and to use the usual find syntax / Containable on the relationships.
Abba Bryant
2010-05-05 23:28:48