--- In [email protected], Shay <[EMAIL PROTECTED]> wrote: > > Hi, Joesph, > > One of the things that I've seen hang up "casual" database developers when > switching between Access and a database server like MSSQL or MySQL is the > change over from an all-in-one package to a more classic application style. > Access does a lot of the code-behind stuff that you will be doing in your > web pages. > > So ... first and foremost, the "rules" for data normalization apply to all > relational databases (the db model which both MS Access/MSSQL Server and > MySQL are based on). Any time that you would create a "lookup" field in > Access, you will use a Foreign Key in MySQL. (In Access, the lookup field is > a foreign key field with the SQL SELECT statement attached to the field by > Access.) > > You *can* have an FK field in MySQL without declaring the relationship and > you *can* create joins on an undeclared relationship. Referential integrity > basically means that you're verifying that the value in your FK field is a > value in the table you're linking to. > > Also, structure-wise, in tbl_CarDetails, *if* tbl_CarModel has a 1-to-many > relationship with tbl_CarMan, you only need to store the FK to tbl_CarModel > as you should be able to access the value in tbl_CarMan through that > relationship. > http://w3schools.com/sql/ will give you an introduction to SQL statement > standards. These are the basics. > -- > > Shay B. @ http://shaybee.deviantart.com/ > (~_~* ) ( *~_~) (*o.o*) (*~_~*) > On Fri, Mar 7, 2008 at 12:39 PM, Joseph [EMAIL PROTECTED] > wrote: > > > Hi > > > > A friend has asked me to make him an application which will allow him > > to log all the cars people want to sell in his auction, but I am > > having a bit of trouble creating the tables, because I don't seem to > > understand when to use foreign keys, or if should be using them at > > all, so any advice on this subject would be greatly appreciated. > > > > I used to use MS Access for all my dB needs, but MySQL handles > > foreign keys totally differently and therefore I am totally confused. > > > > Here is some example of the data I need to use in my tables. The list > > is huge and each "car manufacturer" has lots of "models", roughy 20 > > to 30 models for each make of car. > > > > Alfa Romeo = 145 > > Alfa Romeo = 146 > > Alfa Romeo = 147 > > Audi = 100 > > Audi = 200 > > Audi = 80 > > BMW = X3 > > BMW = X5 > > BMW = Z1 > > > > I have split this data into separate tables, but don't know if it > > makes sense to use foreigns keys, or not. According to the MySQL > > manual, they are only needed to "enforce referential integrity" not > > to join tables. In MS Access there were physical links (I think) to > > other tables, is this not the case with MySQL? > > > > ------------------------------------------ > > tbl_CarMan > > ------------------------------------------ > > cmf_id cmf > > ------------------------------------------ > > 1 Alfa Romeo > > 2 Audi > > 3 BMW > > ------------------------------------------ > > > > ------------------------------------------ > > tbl_CarModel > > ------------------------------------------ > > cmd_id cmodel cmf_id > > ------------------------------------------ > > 1 145 1 > > 2 146 1 > > 3 147 1 > > 4 100 2 > > 5 200 2 > > 6 80 2 > > 7 X3 3 > > 8 X5 3 > > 9 Z1 3 > > ------------------------------------------ > > > > This is the structure of the main table > > > > ------------------------------------------ > > tbl_CarDetails > > ------------------------------------------ > > cd_id > > CarRegNumber > > CarEngineCapacity > > CarTransmission > > CarFuelType > > CarNumDoors > > CarType > > CarServiceHist > > CarColour > > CarTaxed > > CarHPIReg > > CarMOT > > CarChassisNum > > CarPrice > > CarDescription > > cmf_id > > cmd_id > > ------------------------------------------ > > > > Does this look right and do I need to bother with foreign keys for > > this dB?????? > > > > > > > > > [Non-text portions of this message have been removed] > Thanks Shay
First apologies for not getting back earlier, I have been a bit ill this week. Please read this in HTML format because I am using tables. I assumed that dB normalisation would be needed for all relational db's, but I was really hoping for someone to check the normalisation I did on my tables and to tell me if they were normalised as much as possible, because to be honest, I am not at all confident of my abilities to do this properly and really would like some feedback on this. Thanks however for clearing up the confusion in my mind by explaining how ms access created foreign keys, that has helped a lot. Your answers however have prompted another question. You said: "Also, structure-wise, in tbl_CarDetails, 'if' tbl_CarModel has a 1-to-many relationship with tbl_CarMan, you only need to store the FK to tbl_CarModel as you should be able to access the value in tbl_CarMan through that relationship." By this, do you mean that I should create a fk in "tbl_CarDetails" pointing to "tbl_CarModel" (implying that I should delete the "cmf_id" field from "tbl_CarDetails") because I would be able to access this data using a MySQL join, or something? Like this >From To tbl_CarDetails cedi CarRegNumber CarEngineCapacity CarTransmission CarFuelType cmd_id => cmd_id.cmd cmf_id => cmf_id.cmf tbl_CarMan cmf_id cmf 1 Alfa Romeo 2 Audi 3 BMW tbl_CarModel cmd_id cmodel cmf_id => cmf_id.cmf 1 145 1 2 146 1 3 147 1 4 100 2 5 200 2 6 80 2 7 X3 3 8 X5 3 9 Z1 3 tbl_CarDetails cd_id CarRegNumber CarEngineCapacity CarTransmission CarFuelType cmd_id => cmd_id.cmd tbl_CarMan cmf_id cmf 1 Alfa Romeo 2 Audi 3 BMW tbl_CarModel cmd_id cmodel cmf_id => cmf_id.cmf 1 145 1 2 146 1 3 147 1 4 100 2 5 200 2 6 80 2 7 X3 3 8 X5 3 9 Z1 3 Thanks Joesph [Non-text portions of this message have been removed]
