Welcome Guest, Not a member yet? Register   Sign In
Best Practices for Lookup Tables
#1

[eluser]ladyj001[/eluser]
I have a product table with several foreign keys to lookup tables (e.g., categories, formats, etc.)

In my Product_model class, I have a function that returns a product as an associative array (i.e., $product['name'], $product['price'], etc.

With regards to lookup fields, sometimes I want the foreign key (e.g., $product['category_id'] as in the case of generating an update form with a category drop down list. Other times, I want the lookup name as in the case of simple text output. For some views, I may actually need both (e.g., views with text output and form elements).

My question is what is the best way to implement lookup table relationships? There doesn’t seem to be a de facto standard.

Option 1 – In the past, I would simply do a join and return both the lookup id and name (e.g., $product['category_id'] and $product['category_name']). But something has always bothered me about having two fields that reference the same thing. In theory, one could change the value of $product['category_id'] without changing $product['category_name'].

Option 2 – Still use joins; but, for each lookup relationship create a sub-associative array (e.g., $product['category']['id'] and $product['category']['name']

Option 2 – Create a lookup helper or library that would load all needed lookup tables when loaded. Then in my views I could do something like

$this->load->helper('lookup',array('category','format'));

Product Category: <?php echo lookup($product['category_id']) ?>

The helper could also have a select_list function to be used to create arrays for dropdown lists e.g., form_dropdown('category_id',select_list('category'),$product['category_id'])

Just curious how other people deal with lookup tables.




Theme © iAndrew 2016 - Forum software by © MyBB