+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Best Practices (https://forum.codeigniter.com/forumdisplay.php?fid=12)
+--- Thread: Is it better to use Table Lookup strategy rather than saving the text in MySQL? (/showthread.php?tid=71436)
Is it better to use Table Lookup strategy rather than saving the text in MySQL? - kaitenz - 08-13-2018
Hi,
Is it better to use table lookup strategy where my dropdown values are saved in a table then when I save the form on the database, it will only save the ID of the select value. And then use a lookup (Join) for output.
Or just save it as plain text?
Thanks.
RE: Is it better to use Table Lookup strategy rather than saving the text in MySQL? - Pertti - 08-14-2018
While you do need to get the text value from different table, so there's a little bit more work to do when fetching data, IMHO it's better to save ID.
I tend to get base data first, then loop over it and get all unique IDs for separate tables I might need to query, then get only used rows from these tables and loop over original data again and assign text values.
Very basic and dirty example:
PHP Code:
$q = $this->db->where('a', 'b')->get('table_a');
$tableBids = [];
$tableCids = [];
$data = [];
foreach ($q->result() as $row) {
// only keep unique IDs
$tableBids[$row->table_b_id] = $row->table_b_id;
$tableCids[$row->table_c_id] = $row->table_c_id;
$data[] = $row;
}
$q = $this->db->where_in('id', $tableBids)->limit(count($tableBids))->get('table_b');
$tableBids = [];
if ($q->num_rows()) {
foreach ($q->result() as $row) {
$tableBids[$row->id] = $row->text_label;
}
}
// ...
foreach ($data as $row) {
$row->table_b_label = isset($tableBids[$row->table_b_id]) ? $tableBids[$row->table_b_id] : false;
// ...
}
Might be overkill for one or two joins, but I never forget my first day at my current job, where I had to fix someone's silly query that basically joined together all the tables in our database. It crashed DB server on most calls, so that was definitely jumping in the deep end
