Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jul 2006
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    look up list in mySQL tables

    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.

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    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...

  • #3
    New Coder
    Join Date
    Jul 2006
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •