My PHP is good but me SQL sucks =) a little help


#1

We I guess you could say that I am pretty good a dreaming up code but I have done very little with databases and I am a little confused as to how I should ‘type’ I should make my feilds. I will include a list of what I think is correct, let me know if you have any suggestions

The database will be holding information about cars.

ad_color VARCHAR( 15 ) , //should be enough to specify color?
ad_mileage INT( 6 ) , // I hope no one put over a million click on a car
ad_doors SMALLINT( 1 ) , //Will allow up to 9 just in case you get a limo
ad_engine CHAR( 10 ) , //should be enough to specify most engine types
ad_transm SMALLINT( 1 ) , // (auto/manual) 0 or 1
ad_drvtrain SMALLINT( 1 ) , // (rear/front/all wheel) 1,2 or 3
ad_pwrwnd TINYINT( 1 ) , // power window (y/n)
ad_ac TINYINT( 1 ) , // A/C (y/n)
ad_cruise TINYINT( 1 ) , //cruise (y/n)
ad_pwrmiror TINYINT( 1 ) , // power mirrors (y/n)
ad_pwrlocks TINYINT( 1 ) ; //power locks (y/n)

Let me know what you think. I think I got the values right but I’m not familiar with what types to use or what (especially for performance later on)


#2

You could also set the (y,n) , (0,1), and (0,1,2) to [phpref]enum(value1, value2, etc.)[/phpref]. That will give you more rigidity in what can be entered. Only those inputs will be accepted.


#3

MySQL does support foreign keys if you use InnoDB storage type.

http://www.mysql.com/doc/en/InnoDB_fore … aints.html

Subqueries are availible in MySQL 4.1, which is currently in alpha. Procedures and triggers are availible in 5.0, but it’s still in development (you can get the sources with BitKeeper). Procedures have been working for months already, it’s just not stable enough yet to be released.