Is it better to spell out values in a field or use something like numbers?

For example...

Gendera.) Male/Femaleb.) M/Fc.) 0/1d.) 1/2

Marital Statusa.) Single/Marriedb.) S/Mc.) 1/2

Residencya.) Resident/Non-Residentb.) 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
—
2012-05-05T06:19:17Z —
#2

i would spell them out

simpler, isn't it

Mandes
—
2012-05-05T22:55:55Z —
#3

^ 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 thanif($row['Field1'] == '0'){

DoubleDee
—
2012-05-06T03:53:12Z —
#4

Mandes said:

^ 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 thanif($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
—
2012-05-06T04:11:28Z —
#5

DoubleDee said:

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
—
2012-05-06T04:17:59Z —
#6

r937 said:

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
—
2012-05-06T04:21:20Z —
#7

DoubleDee said:

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

DoubleDee said:

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)

DoubleDee said:

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

no

ENUM is different, and evil

DoubleDee
—
2012-05-06T04:54:01Z —
#8

r937 said:

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.

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

DoubleDee said:

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
—
2012-05-06T05:10:27Z —
#10

r937 said:

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, genderFROM 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
—
2012-05-06T05:15:07Z —
#11

DoubleDee said:

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
—
2012-05-06T05:45:51Z —
#12

r937 said:

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.

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
—
2012-05-06T05:59:13Z —
#13

DoubleDee said:

How is my Look-Up Table enforcing anything?

by use of a foreign key to the lookup table

DoubleDee said:

Do I need to add some constraints?

yes, the foreign key

DoubleDee said:

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)

DoubleDee said:

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
—
2012-05-06T06:05:20Z —
#14

r937 said:

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
—
2012-05-06T06:38:44Z —
#15

no, not on every column

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

DoubleDee
—
2012-05-06T06:45:46Z —
#16

r937 said:

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
—
2012-05-06T06:55:40Z —
#17

DoubleDee said:

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

DoubleDee said:

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
—
2012-05-06T09:30:03Z —
#18

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
—
2012-05-06T11:11:52Z —
#19

Mandes said:

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
—
2012-05-06T19:30:56Z —
#20

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.