...

View Full Version : look up list in mySQL tables



irish87
07-21-2006, 07:14 PM
I am in the process of building a fantasy f1 website and m relatively new to this sql business. Below is an example of a problem I have along with a couple of the tables in my database.

DriverTbl
|DriverID|DriverName|DriverCost|

AccountTbl
|AccountID|Driver1|Driver2| etc...etc...

My problem is simply this...

I was wondering that when creating the db should I set the data type of |Driver1| and |Driver2| etc.. to enum and then enter all the different drivers that the end user can choose from in the Length/Values area? or, should I just set the data type to text and then using the perl script and entering a MySQL query/queries, create a drop down list which only uses the field |DriverName| from the DriverTbl? so that when the user goes to select their Drivers they will do so using a drop down list on the webpage.

Irish87.

GJay
07-21-2006, 07:42 PM
Neither.

You store the drivers' ids in the Account table, and to create the drop-down use the drivers table.

If you're using tables that support foreign-keys, then enforce the constraints properly with something like:
ALTER TABLE accounts ADD CONSTRAINT driver1_fkey FOREIGN KEY (driver1) REFERENCES drivers(id) ON UPDATE CASCADE ON DELETE SET NULL;
(and the same for driver2).

If you're not using such tables, then you'll have to just check for validity in the code- using an enum isn't really practical, as what if there's a new driver added? you don't want to be messing with table structures for such occurences...

irish87
07-21-2006, 07:53 PM
This will probably sound stupid but does it have to be the Drivers'ID that is stored in the accounts table? could it not be their name? or does it have to be the Drivers' ID because it is the primary key in the DriverTbl, Ie. to make it a foreign key?

The reason I ask this is because I was thinking along the lines of, to remain normalised the table must not incorporate duplicate data. but surely if two different accounts have the same driver selected then the Drivers'ID number in both those records would be the same. Ie. there would be no difference in having the DriversID stored several times than having their name stored several times in the accounts table - Correct? could you plz clarify this for me as it all seems a bit grey!!

Irish87



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum