4 responses to A story of hubris, horror, and SQL lookup tables

  1. I was reading out your take on lookup tables, and wonder your thoughts on a good solution. For example it seems like you come to the conclusion that the basic lookup table is the way to go.

    What is your thought on say a persons name field? For example a lookup table has a persons full name in it with an ID that is reference in other tables. How do you lesson the load on mysql when there is many tables that lookup the full name value since those tables onlynhave the foreign key.store the table as session array son that it is a one time call for the data. Some sort of master class that creates it, issue though of per page having itnrun again.
    Any thoughts?

    • Sorry I missed this reply earlier: I get lots of spam comments and it can be hard to tell the real ones sometimes!

      I don’t think a name field is a good fit for a lookup column. Good lookups have these attributes are not unique, and are one of a limited group. Title is a good example: for each individual the possible titles are probably limtied to Dr, Mr, Ms, Miss, Mrs, and perhaps Sir. First names have such a variety of spellings and so on that they don’t make good lookups. The exception might be if you have massive scale: for example if you have millions of rows. But even then, there will be many non-English names.

      OK, I just re-read your question and I think I understand what you mean now. I’ll try to paraphrase it:

      You have a PHP web app were you make use of many pages where you use a list of People. You want to lookup the names frequent in your code, and you want to lessen the load on SQL by caching the people table. If you use the list of names multiple times in a single page, you can store it in some kind of global array or say in a singleton class. If you want to persist it across pages then yes an array that you store in the session could work nicely. I do this by buildign a lean array (ie just the fields I actually need – in your case ID and whatever name fields). Then I serialize it to the session on __destroy() and unserialize it on the next startup – this depends on whether it’s objects or just an array.

      Hope that helps!

Trackbacks and Pingbacks:

  1. Database Schema Design- Yii Framework Tutorial Sess. 2 - Go Learn PHP - August 28, 2013

    [...] role_code – we’ll default this to ‘user’ as a common sense. We’re going to use enums here. I considered a lookup table briefly; see this post for more discussion of lookups vs enums. [...]

Leave a Reply


Text formatting is available via select HTML.

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>