Welcome Guest, Not a member yet? Register   Sign In
Is it better to use Table Lookup strategy rather than saving the text in MySQL?
#1

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.
Reply
#2

(This post was last modified: 08-14-2018, 12:36 AM by Pertti.)

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 Big Grin
Reply




Theme © iAndrew 2016 - Forum software by © MyBB