Last post Feb 15, 2018 09:26 AM by Omkar Mhaiskar
Feb 15, 2018 08:59 AM|itismesa|LINK
Good day to everyone,
I have some fields in my application like :
* Status (Pending, Approved, Rejected)
*Gender (Male, Female)
* Education (Elementary, Secondary, High School, Degree)
Each of these fields can have one value of a few options that user will select from a drop down list. I am wondering if I should store a code in the database to represent each value instead of storing the original text value.
For example, for the Status field I can create a field of type Tinyint in the main table and store 1 for Pending, 2 for Approved and 3 for Rejected. At the same time in the lookup table, I add a row for each code with its text value. In this case I have
to do a join between the main table and lookup table to present the information to users.
The other option is to store the text value as is in the main table and no need to have it stored in the lookup table.
What do u think? What is the best practice?
Feb 15, 2018 09:26 AM|Omkar Mhaiskar|LINK
Use the first approach as mentioned in the post. So using this approach you will achive normalisation and better performance in your application. Refer below article in which mentioned advantages of normalisation.
so basically your main table has refernce of child table id.
Using this you can refer below query to retrive data as per your requirement.
SELECT M.*, St.Name, gd.name, ed.name From tblMain M
INNER JOIN tblstatus st ON st.statusid=m.statusid
INNER JOIN tblGender gd ON gd.genderid=m.genderid
INNER JOIN tblEducation ed ON gd.Educationid=m.Educationid
Mark As Answer If it is helpful.