Spell out values or not?

[DoubleDee]

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?

Thanks,


Debbie

[r937]

i would spell them out

simpler, isn't it

[Mandes]

^ what he said

+

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'){

[DoubleDee]

^ what he said

+

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'){

How would you ensure Data Integrity if things are spelled out?

Some people recommend using an Integer field like I have now, but also have a Lookup Table with the spelled out names.

Thoughts on that?


Another option would be to use the ENUM Data-Type...


Or I could just hope my code holds and that someone doesn't go into the back-end and types "Woman"...


Debbie

[r937]

Some people recommend using an Integer field like I have now, but also have a Lookup Table with the spelled out names.

who are these people? are you posting these questions elsewhere as well?

to ensure integrity, use a lookup table, and use the spelled out names without a number

as for ENUM, it is the spawn of the devil, and should be avoided

[DoubleDee]

who are these people?

Just a few people I know, and also some articles I saw online.

are you posting these questions elsewhere as well?

Nope.

to ensure integrity, use a lookup table, and use the spelled out names without a number

So create a one field table like this...

gender
---------
gender

Isn't that a real drag when you build queries?

What if I need 10 Lookups? Then I'd have to do 10 INNER JOINS?!

as for ENUM, it is the spawn of the devil, and should be avoided

Isn't that what we are doing with a one-field table?!


Debbie

[r937]

Isn't that a real drag when you build queries?

nope, it's simple

it's also completely obvious when you read the sql what the intention is

What if I need 10 Lookups? Then I'd have to do 10 INNER JOINS?!

possibly... but if you use spelled-out names instead of numbers, you won't have to do any joins at all

do not make the mistake of building a "one true lookup table" (search that phrase for reasons why)

Isn't that what we are doing with a one-field table?!

no

ENUM is different, and evil

[DoubleDee]

possibly... but if you use spelled-out names instead of numbers, you won't have to do any joins at all

I'm not understanding you.

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;

Right?


Debbie

[r937]

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.

correct, you need the lookup table so that data integrity is assured for inserts to your users table

So in order to use that Lookup Table I would need to do...

no, you don't need that join

SELECT id, name, gender
FROM user AS u

[DoubleDee]

correct, you need the lookup table so that data integrity is assured for inserts to your users table

no, you don't need that join

SELECT id, name, gender
FROM user AS u

My brain is fading...

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?


Debbie

[r937]

do I somehow need to grab values from a Look=up Table

yes, that's how you construct the dropdown menu

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

[DoubleDee]

yes, that's how you construct the dropdown menu

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

I'm getting more confused here...

I create my User table with these fields...

- id
- name
- gender
- eye_color

And then I create a Lookup Table with this one field...

- gender

In my web-form I have some form control that offers "Male" and "Female" as choices.

When the form is submitted, I do an INSERT like this...

	$q3 = "INSERT INTO user(id, name, gender, eye_color)
			VALUES(?, ?, ?, ?, ?)";

How is my Look-Up Table enforcing anything?

Do I need to add some constraints?

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...


Debbie

[r937]

How is my Look-Up Table enforcing anything?

by use of a foreign key to the lookup table

Do I need to add some constraints?

yes, the foreign key

And *if* the database is enforcing that "gender" must be either "Male" or "Female" then what happens if that isn't the case?

you get an error saying that foreign key integrity has been violated, and the insert fails

note you can catch the error in php and issue your own user-friendly error message (but don't ask me how to do that, as i don't do php)

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?

front-end validation can be bypassed

[DoubleDee]

front-end validation can be bypassed

So how far do I take validation on the back-end?

Do I need a data validation constraint on every field?



Debbie

[r937]

no, not on every column

user's last name, for instance, would be horrendous to set up a lookup table for

[DoubleDee]

no, not on every column

user's last name, for instance, would be horrendous to set up a lookup table for

But so you would recommend a Lookup Table on anything that has a finite universe, e.g. gender, marital status, U.S. States, (possibly) year born, etc?

Is there a way in MySQL you can write other Constraints that check things like isNumeric, isValidZip, isCurrency, etc?


Debbie

[r937]

But so you would recommend a Lookup Table on anything that has a finite universe, e.g. gender, marital status, U.S. States, (possibly) year born, etc?

anything? no, it's up to you which ones require strict conformance

for instance, year born i would simply have as numeric (and the form field would be a text box, not a dropdown) -- so if my user enters 1492, so be it

it really depends on you and your needs

Is there a way in MySQL you can write other Constraints that check things like isNumeric, isValidZip, isCurrency, etc?

in general, no

although the obvious "is numeric" constraint is enforced by the datatype of the column being one of the numeric datatypes (like INTEGER)

[Mandes]

Debbie

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.

[r937]

You can avoid these lookup tables by just making sure that you check for validity within the php script that writes to the database.

i completely disagree

the lookup tables, involving foreign keys for data integrity, are best practice

to advise someone to "avoid" them is, what shall i say, very disagreeable

other stuff, like zip code regular expressions, yes, you would/could/should do those

[Mandes]

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.

[r937]

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.

because php might not be the only way to enter data into the table

because validating it with php is extra work to code, requiring code modifications whenever the set of valid values has to change

there's two good reasons, i'm sure there are others

[Mandes]

Mmmm, weak arguement IMO

OK I agree if you were using lots of different scripts written in different languages then using foreign key refs to validate would be a good idea, but with the case in hand most of the data will have to be validated in PHP (or whatever language is being used) as SQL cant validate it on its own, so adding one extra line to validate the gender would be niether here or there, and would be less than the coding required to handle the error generated by a failed foreign key validation.

Thanks for the enlightenment anyway