Caching versus Joins

Q: I have a large structure in my database with a lot of entities (sales, companies, categories, ...). Do you advise to make joins between a larger number of tables to display info on my webpages.

A1: I think the mySQL server is up to this task and see no problem in using statements like: select categories.name, sales.name from sales left join companies on sales.company = companies.id left join categories on companies.category = categories.id  where sales.creates > ...  etc.

A2: However if you feel things could get easier by having a list of for example categories(key,name) available during the render phase in your view files, you should definetly do it.

Q: How do I best implement this caching?

A: I would cache them in my application object (Cody makes for each site a separate Application object).

• In my Controller I would check my cache is already in the app object:
  

  if (typeof this.app.categories === "undefined") { ... }

• If not add them with an SQL in the form of an object:
  

  var self = this;
  self.app.categories = {};
  self.query("select id, name from categories", [], function(err, results) {
    if (err) { throw new Exception(err); }
    for (var iR in results) {
      self.app.categories[results[iR].id] = results[iR].name;
    }
  });

• In your views you can then use this cache to display names where you have id's.
  

  <% app.categories[ curr_comp.category_id ] %>