mysql table options

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pepe_lepew1962
Forum Commoner
Posts: 44
Joined: Thu Nov 20, 2008 10:29 am

mysql table options

Post 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
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: mysql table options

Post by novice4eva »

I think if your tables are normalized then there is no need for redundancy!!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: mysql table options

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