Topic: What can be "stored" in the app instead of in a DB table?

Having not come from a computer science/programming background, I'm having an internal struggle with this smile

I always have a lot of little lists of options that are needed throughout my applications and probably won't be changing very much, if at all. Some examples:
- User's title (Dr, Mr, Mrs, Ms, etc.)
- User's suffix (II, III, Jr, etc.)
- Directions (North, South, East, West)
- Answers (Yes, No, Sometimes, etc.)
- and so on

To handle these I can think of three (or maybe two and a half) solutions, but I would like some feeback from the pros.

1) Create arrays for the lists inside a model to use in generating select options and then store the string in a database column. For example have TITLES = %w(Dr Mr Mrs Ms ...) in my User model, create options using it, and then store "Dr" or "Mrs" in the "title" column of my users table. There are some database normalization issues here, but it cuts down on querying and/or joining. Also, if it is a list that would "never" change (e.g., North, South, East, West), does it need to be stored in a database table?

2) Create a plethora of models and tables for each list and establish all the has_many and belongs_to relationships. This seems to be more correct, but is it necessary all the time?

3) This one is closely related to #2. In my last application I saw there were going to be a lot of lists where I have simply the option name and id, so I created an options table and implemented some Single Table Inheritance. It still required creating models for each, but cut down on my tables. Not sure if this is the best idea or not...

So in addition to your thoughts on the preceding, I guess I am asking, when is it best to just have things coded in the application, and when should you stick it in a database?

Last edited by ogicu812 (2007-04-18 20:42:09)

Re: What can be "stored" in the app instead of in a DB table?

My best advice: follow the common principle of KISS (Keep It Simple (Simon|Stupid|...).  It's very easy to create a class constant which is an array of some options.  If you find yourself changing it a couple times after the initial approach, then think about re-mapping the options into a table.  With a migration, this is very simple.
If I was certain the options were going to change, I'd make it a table from the start.  Otherwise, it's a premature optimization, an engineer's biggest time-waster. - Brewery and Brewpub management powered by RoR

Re: What can be "stored" in the app instead of in a DB table?

If there is any possible need for internationalization or locaization in your apps future put them in the database.

Re: What can be "stored" in the app instead of in a DB table?

Thanks for the replies! Good advice.

Re: What can be "stored" in the app instead of in a DB table?

Remember though, if you put all these things in a database your going to end up running more or larger SQL queries and keeping the number of queries down to a minimum is always a good thing.

Even if they are going to change, whats the worst that can happen with a list of titles?

- You add a new one? Fine, not going to break any existing data
- You rename one? OK, this will stop one entry however a quick SQL query will replace any old entries.
- You delete one? Again, a SQL query can replace it with an alternative.

I store titles and small amounts of meta data in arrays or hashes as you described.

Last edited by shadow (2007-04-19 12:34:22)