+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Best Practices for Lookup Tables (/showthread.php?tid=12638)
Best Practices for Lookup Tables - El Forum - 10-26-2008
[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.