Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Newbie getting myself confused!

    Hi Folks,

    I am learning SQL using MySQL after a 10 year break from programming Looking back at Uni, I seemed to remember databases being a bit "smarter" than I seem to be finding them.

    Here is an example of a car database with two tables, containing information about the car manufacturer and the car model:

    Code:
    manufacturer_table
      manufacturer_id
      manufatuerer_name
      manufacturer_founded
      manufacturer_defunct
    
    model_table
      model_id
      model_name
      manufacturer_id

    I realise I could do a search like:
    Code:
    select manufacturer_name, model_name from manufacturer_table, model_table where model_name="Mondeo" and model_table.manufacturer_id = manufacturer_table.manufacturer_id

    (Maybe the SQL isn't perfect, but it gets my question across!)

    Which should output somthing like:

    Code:
    -----------------------------------
    |manufacturer_name | model_name|
    |----------------------------------
    |Ford                     | Mondeo       |
    -----------------------------------

    But what I am getting confused about is the relationships between the tables. I have manually searched and matched up the information I wanted using the select statements. I seem to remember in Uni doing one-to-many relationships, but I can see that there could be a way of the manufacturer_table (say Fiat) having a list of models *inside* it - to get a list of models that Fiat made, you would have to search the model_table and then refer back to the manufacturer_table to see if was a Fiat, rather than just being able to see a list of all the model_id's somewhere within the manufacturer_table.

    I thought that databases were a bit smarter than this for some reason, and before I continue learning and writing my project, I just wanted to check that I wasn't missing something!

    So I guess my questions is - is the database aware in anyway of the relationships between the tables, or do you have to manually compare keys in the select statements? In my example, would I be correct in searching everything model_table for where model_table.manufacturer_id is Fiat?

    If this is the case, what difference does one-to-many relationships, or many-to-one or one-to-one relationships make, or am I confusing database design with implementation... or am I generally going crazy

    Thanks in advance for all your replies!

    Andy.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AndyMcCall View Post
    is the database aware in anyway of the relationships between the tables, or do you have to manually compare keys in the select statements?
    the latter

    i could go into details but the gist of it is that you can join tables based on whatever columns you want (whether it makes sense to or not)

    Quote Originally Posted by AndyMcCall View Post
    what difference does one-to-many relationships, or many-to-one or one-to-one relationships make
    all the difference in the world

    okay, there is no difference between one-to-many and one-to-one in the way they are implemented

    and of course there is only one way of implementing a relationship -- using foreign keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    4
    Thanks for the reply!

    So is the concept of a foreign key only a concept?

    For example the manufacturer_id key in the model_table in my example could be called anything at all, so long as the person building the select statement new that manufacturer_id pointed back to manufacturer_table.manufacturer_id.

    Consider there a third table:

    Code:
    tyre_table
      tyre_id
      tyre_name
    There would be nothing to stop a programmer building a select statement that incorrectly matching manufaturer_if from the model_table up to the tyre_id in the tyre_table giving output like:

    Code:
    ------------------
    |Dunlop    |  Mondeo|

    This seems strange that so much information about the relationships and links between the tables of the data is mapped and kept outside of the database, and for all anyone knows "in the programmers head".

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AndyMcCall View Post
    So is the concept of a foreign key only a concept?
    no, it's actually implemented into the structure of the tables

    for example,
    Code:
    CREATE TABLE models 
    ( model_id INTEGER NOT NULL PRIMARY KEY 
    , model_name VARCHAR(99) NOT NULL
    , manufacturer_id INTEGER 
    , FOREIGN KEY ( manufacturer_id ) REFERENCES manufacturer_table ( manufacturer_id ));
    Quote Originally Posted by AndyMcCall View Post
    There would be nothing to stop a programmer ...
    other than quality reviews of his deliverables, no -- but he would never get it into production because the results of the query would be wrong

    Quote Originally Posted by AndyMcCall View Post
    This seems strange that so much information about the relationships and links between the tables of the data is mapped and kept outside of the database, and for all anyone knows "in the programmers head".
    actually, most of it is kept in the database

    do a search for INFORMATION_SCHEMA
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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