Page 1 of 1

mysql table options

Posted: Thu Nov 20, 2008 12:26 pm
by pepe_lepew1962
Hello;

I am more than green regarding mysql and php but due to business economics I am forced to

convert from ms access to mysql and desperately need assistance. I have created a database

and multiple tables, all associated with joins on ( user.id ). In the example below,

questions 1 and 3 have the same possible answers, this carries on throughtout the table

where the possibilities are the same. When it comes to filling the fields with answers I

could have the field varchar(??) based on the data. Having thousands of records would mean

a lot of space is taken up with same data. This would make displaying the records later

much easier as the fields have the full information already, but again, lots of space is

wasted.

My question/problem is "Can I use only 1 character for the answer and when it comes to

displaying the information switch it over to actual words. For instance, use char(1) for

fldquest01 ( the field for question 1 ) and have populated with 0, 1, 2, or 3 ( none, low,
moderate, high )? Either via mysql/php display the proper word for the data on the

webpage. I understand that the php file would be that much bigger, but would it be quicker

than redundant information in the tables?

I am trying to figure this out before I finish the tables to save double work or changing

something that will ultimately happen anyways. Can anyone help me with a solution or point

me with the correct language ( php, java, html ).


1. How are your energy levels?

None
Low

Moderate

High


2. Do you have trouble falling asleep or staying asleep?

Sometimes

Frequently

Rarely


3. How would you rate your level of confidence?

None
Low

Moderate

High


4. How often do you suffer minor ailments?

Frequently

Occasionally

Rarely

Re: mysql table options

Posted: Fri Nov 21, 2008 12:48 am
by novice4eva
I think if your tables are normalized then there is no need for redundancy!!

Re: mysql table options

Posted: Fri Nov 21, 2008 1:26 pm
by califdon
There are several options. You could use the field type 'enum' (enumerated data), or you could store integers that are Foreign Keys into a table of text equivalents, which is what novice4eva was referring to. You may want to search Google for 'database normalization'. The amount of storage (or response time) is not really going to be an issue unless you will have many tens of thousands of records, but the principle of data normalization is important in database design.