Hi i am using php and mysql on my project and i wanted to know which is better (all aspect).
I am expecting a big amount of data in the database.
I want it to search faster.


Should i store the real value or a reference value.
1) Have php proccess it? Meaning I will store values in the database as integer/tinyint and then create a function to know the value to output. ie. if 1 then value is "north" elseif 2 then value is "east" and so on. values will not change.

let say i will have at least 20 of this function.

database design
id direction
1 1 (1=north)


output (use php to display the output)
north



2) Dump all data to database.

database design
id direction
1 north

Will the data change overtime, or are they fixed?

If the data can change, better store them in database so you don't have to keep changing your PHP code and just update the data in database as needed.

If the data is fixed, then just store it in PHP. Maybe you can just store it as array and access the record as needed.

$data = array (1 => 'i am one', 2 => 'i am two', 3 => 'etc');
echo "{$data[1]}"; // will output 'i am one'

I am guessing you are asking whether to store number of days as text or number. There is no reason whatsover to store the text. You can easily get the sentence from the number

#http://pear.php.net/package-info.php?package=Numbers_Words must be installed
include("Numbers/Words.php");

$row;//The row corresponding to your quert fetched from database

$nw = new Numbers_Words();

$sentence="I am ".$nw->toWords($row['howmanydays']);

It doesn't take a lot of time either.

Why not store the text? Good question

1. Text Occupies more space:

So your very large database will be using up a lot of unnecessary space that could easily be reduced by using numeric datatype

2. Numeric Operations

You can perform queries like where howmanydays> 10 which you can't if you store it as text

3. Internationalization

If you store it directly as text, Internationalization becomes more difficult. Now you can simply fetch the sentence from a language file and fill in the variable

Thanks for clarifying! If directions/ constants can never change, you can use an array of constants & and a couple of functions in PHP to encode/decode them from UI <-> internal & database values.

If there's not naturally an numeric value or semi-numeric sequential ordering, I would probably prefer to encode them as CHAR(1) or short VARCHAR constants. More readable in the DB as "N", "S", "E", "W" etc.

I use this scheme for record statuses & a soft-delete scheme -- active "A", on-hold "H", deleted "D". (Records are flagged as "D" deleted and hidden, rather than permanently being deleted from the database.)

OTOH for "workflow" type activity, in one place I use numerics to track WORKFLOW_STATE -- since the states can be considered as a roughly ascending sequence.

If directions/constants may change occasionally over time, but require application-code support -- they can also be treated as constants. For example: Tax Types. You can't implement a new "tax type" without code & knowing how to calculate it.

If OTOH the values may change or be added more frequently, and without application code-changes, then they should be configuration/ or a dimension table. For example: Tax Rates, Cities. You can implement a new or changed percentage tax-rate, for an already existing tax-type, without code change.