Is it better to spell out values in a field or use something like numbers?
For example…
Gender
a.) Male/Female
b.) M/F
c.) 0/1
d.) 1/2
Marital Status
a.) Single/Married
b.) S/M
c.) 1/2
Residency
a.) Resident/Non-Resident
b.) 1/2
and so on…
Also, if you say something like “Use a Boolean for Gender” then do you create a Look-up Table as well?
I’m asking this more from the standpoint of determining if I should be creating PHP Functions to translate database values and display what I want, or if I should rely on the database to do all of the work for me?
it makes your code easier to understand next year when you have to change it. And you wouldnt consider naming your fields ‘field1’ ‘field2’ would you !!
if($row[‘Gender’] == ‘Male’){
is so much quicker to understand than
if($row[‘Field1’] == ‘0’){
If I have a User table, and in it is a Gender field, and I want to be sure users can only choose “Male” or “Female” and that I only display those two chooses, then I would need a Lookup Table.
And as I understand you, it should only be one field (i.e. “gender”)
So in order to use that Lookup Table I would need to do…
SELECT id, name, gender
FROM user AS u
INNER JOIN gender AS g
ON u.gender=g.gender;
Since my front-end is HTML/PHP, and I want to ensure that a User selects either “Male” or “Female” and that only one of those two values is written into my database, then do I just use something like a Drop-down Menu to ensure that one of those two values is used?
Or - as I was thinking above - do I somehow need to grab values from a Look=up Table and write those into the User table?
but when the form is submitted, the field value is automatically checked for correctness (i.e. you don’t use a separate lookup query) by the foreign key in the users table
And if the database is enforcing that “gender” must be either “Male” or “Female” then what happens if that isn’t the case?
Now that I think about it, I’ve actually always relied on my Front-End to do the data validation and ensure that the right data goes into the right fields?
Sounds like you are talking about something different…
You can avoid these lookup tables by just making sure that you check for validity within the php script that writes to the database. Youre going to have to have a check in there for the examples above that cant be handled by the database alone, so you may as well put all the checks in the same place and be done with it.
Anybody that suggests that you replace meaningful code for numbers needs shooting and putting out of their misery IMO , the only reason why you would do such a thing would be to obfuscate the code to prevent (well make it harder anyway) someone from understanding your scripts. But that includes you too !!
So you code that receives the posted data from your form will check each field for validity before attempting to insert the data in the table. The year born example could check that there are only 4 numbers and that the year entered is between 10 -90 years ago. For zipcodes, email addresses, telephone numbers etc there are readily available regex’s for checking these and PHP has filter fuctions too for this purpose.
OK Rudy enlighten me … please explain why she should have a lookup table that validates the gender if she has already validated the gender 100% in php before she writes to the database.