StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to organize storage of descriptions for dropdowns list items
primarykey
Id
19371666
data
AcceptedAnswerId
19371775
AnswerCount
1
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2013-10-15T00:59:11.510
FavoriteCount
0
LastActivityDate
2013-10-15T01:12:05.830
LastEditDate
2013-10-15T01:05:24.273
LastEditorUserId
1854930
OwnerUserId
1854930
ParentId
0
PostTypeId
1
Score
0
ViewCount
31
LastEditorDisplayName
text
Body
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 <pre><code>SELECT ... user_status ... FROM users WHERE user_id = 5 </code></pre> then i pass to template language, i use Smarty v3 and then inside page template i do something like <pre><code>{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} </code></pre> 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 <pre><code>user_status_id status_name 1 New User 2 Application Accepted 3 Payment received ... </code></pre> then in main query for each page i do something like this <pre><code>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 </code></pre> and in page i just use <code>{$users[0].status_name}</code> 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 <pre><code> 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 ... </code></pre> Then i just do same as method 2 except i left join this table based on id_number e.g. <pre><code>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 </code></pre> 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?
Tags
<php><mysql><smarty><organized>
Title
How to organize storage of descriptions for dropdowns list items
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USPetja Zaichikov
UserOwnerUserId
1. USPetja Zaichikov
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.