How to organize storage of descriptions for dropdowns list items

Question

Say i have many tables in database that contain numeric id e.g. fields user_type, user_status, user_currency etc.

now i do have to display this information along other info on my pages of my website, say i have 10 pages where some of above info will be printed

METHOD 1 when i write query i do something like

SELECT ...
       user_status
       ...
    FROM users 
       WHERE user_id = 5

then i pass to template language, i use Smarty v3

and then inside page template i do something like

{section ...}
   ...

   {if $users[0].user_status == 1}
      New User
   {elseif $users[0].user_status == 2}
      Application Accepted
   {elseif $users[0].user_status == 3}
      Payment received
   {/if}

   ...
{/section}

so same code basically goes into 10 different pages and when i change something or add new status, currency etc i got to go to each page and add it to the if statement

I think this method is very unreliable and time consuming

METHOD 2 so i started to do it like this i create another table with user statuses e.g. tbl_user_statuseds

user_status_id  status_name
    1              New User     
    2              Application Accepted
    3              Payment received
    ...

then in main query for each page i do something like this

SELECT ...
       tbl_user_statuseds.status_name
       ...
    FROM users 
       LEFT JOIN tbl_user_statuseds ON tbl_user_statuseds.user_status_id = users.user_status 
           WHERE user_id = 5

and in page i just use {$users[0].status_name} this takes out editing on each page if i add new status. All has to be done is add new line in database table statuses once

However with second method i have now about 10 tables in database that just contain the numerical id descriptions tbl_user_statuseds, tbl_currency_names, tbl_user_sex etc.

METHOD 3 i wonder would it be a good practice to use once table to store this ids? E.g.

tbl_id_labels

    id    id_type  id_number  id_label
    421        2        1        New User     
    422        2        2        Application Accepted
    423        2        3        Payment received
    424        8        1        USD
    425        8        2        EURO
    426        5        1        Male
    427        5        2        Female
    ...

Then i just do same as method 2 except i left join this table based on id_number e.g.

SELECT ...
       tbl_id_labels.status_name
       ...
    FROM users 
       LEFT JOIN tbl_id_labels ON tbl_id_labels.id_number = users.user_status AND tbl_id_labels.id_type = 2 
           WHERE user_id = 5

This way i only have one table with all labels for numeric id's i have in my system. If i have to add one in the future its not too difficult, all that matters is id_type and id_number, field id is just for compatibility.

Do you see any problem with third method, or can you advice better approach?

score 0 · Accepted Answer · answered Oct 15 '13 at 01:12

0

Method 1 is obviously inconvenience to support, so I don't suggest it

Method 2 is good option, just don't use LEFT JOIN, INNER much better

Method 3 is not very good option, because of performance, so I suggest 2nd

Also, just think - why do you need to store constant data in database? Create 10 php functions which will use simple switch/array to output title for ID? - this will be the the fastest approach

answered Oct 15 '13 at 01:12

Iłya Bursov

23,342
4
33
57

I dont think third method will lag database as long as i create index for column id_type and id_number, i cannot see total size of table go more than couple hundred records at any time. I like storing field names in PHP approach, can you give me an example? – Petja Zaichikov Oct 16 '13 at 22:34
2nd option will work faster than 3rd, this is why I wrote about performance, next issue about example: just functions like: `function getStatusForId($id) { switch...}` and in template something like `echo getStatusForId($users[0].user_status)` – Iłya Bursov Oct 16 '13 at 22:38
Actually i kind of prefer method 3 even if its a bit slower, or two because this way you can create page in admin interface where end user (client) can rename statuses or add new status. I mean of course if he edit delete new status it will break program, but i also believe that program can be created in the way where all logistics (process for user or system) can be defined in database table not php – Petja Zaichikov Oct 24 '13 at 21:06
You could create two or three databases on different servers for different purpose this way it wont slow down the site. – Petja Zaichikov Oct 24 '13 at 21:07
Say one table is user info other table is user document third table sets relationship between first two tables and fourth table sets process at which point in user process documents required to be collected etc. Its Like lego, of course it not 100% done in database and some stuff you still need to code in php, but i think logic should be in database as much as possible, this makes site more reusable and universal. Like CMS system where client can basically "program" the site the way he wants it to work using back end and saving all in database... – Petja Zaichikov Oct 24 '13 at 21:09

How to organize storage of descriptions for dropdowns list items

1 Answers1