Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    24

    Unanswered: associative table or a directly foreign key,what is better?

    Hey,

    I am working with hibernate and i want to map my Objects to tables structre.

    There is two ways to map relation:
    1. with associative table
    2. directly foreign key (i mean without associative table)

    Can you please explain what are the advantages for each way?


    Thank you

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    What you do should really be dependent upon the relationship between the information. Firstly, forget classes/objects, as really this is only relevant to programming and will confuse the situation. Trust me i've been round in circles on this one (unless you're dealing with composite menu structures, but that's a whole different kettle of fish).

    An example reason to use a direct foreign key :
    You have authors and books...
    Your authors have written many books, but your books can ONLY have one author (i know this isn't always the case but it's being modelled this way).
    Thus your table structure is one-to-many, so you use a foreign key for the author_id in the book table
    e.g.
    Code:
    CREATE TABLE authors (
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      name VARCHAR(30)
    ) ENGINE=InnoDB;
    
    CREATE TABLE books (
      isbn VARCHAR(10) NOT NULL UNIQUE,
      title VARCHAR(50) NOT NULL,
      summary VARCHAR(255),
      author_id INT UNSIGNED NOT NULL,
      CONSTRAINT FOREIGN KEY (`author_id`) REFERENCES authors(`id`)
    ) ENGINE=InnoDB;
    Some example data might include :

    Code:
    `authors`
    =========
    id name
    =========
    1  mido
    2  hitchcock
    3  einstein
    4  newton
    
    `books`
    =====================================
    isbn   title                 summary            author_id
    =====================================
    0093  'amazing grace'  'i am a summary'  1
    0012  'another title'     'summary no#2'   1
    0123  'same author'     'small summary'    1
    0141  'different author' 'erkjvakejrhvjk'    4

    And thus in order to retrieve all the books by one author (Mido) you have the following query:
    Code:
    SELECT isbn,title 
    FROM authors a
    JOIN books b ON a.ID=b.author_id
    WHERE a.Name = 'mido'
    Note in this example above there is NO NEED to have an associative table because a book can ONLY have one author!

    I will do an example of an associative relationship shortly.
    Last edited by aschk; 04-20-07 at 10:42.

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    An associative table so be used for a many-to-many relationship. A good example of this would be cars and drivers. One car can have many drivers (not at the same time obviously, but can be registered with multiple drivers) and one driver can be able to drive more than one car... Thus a many-to-many relationship is to be formed which requires an associative table.

    e.g.
    Code:
    CREATE TABLE cars(
      id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      registration VARCHAR(8) NOT NULL UNIQUE,
      make VARCHAR(20) NOT NULL,
      model VARCHAR(20) NOT NULL
    ) ENGINE=InnoDB;
    
    CREATE TABLE drivers(
      id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(20) NOT NULL,
      license_number VARCHAR(40) NOT NULL UNIQUE
    ) ENGINE=InnoDB;
    
    CREATE TABLE drivers_cars_map(
      driver_id INT UNSIGNED NOT NULL,
      car_id INT UNSIGNED NOT NULL,
      CONSTRAINT FOREIGN KEY (`driver_id`) REFERENCES drivers(`id`),
      CONSTRAINT FOREIGN KEY (`car_id`) REFERENCES cars(`id`),
      PRIMARY KEY(driver_id,car_id)
    ) ENGINE=InnoDB;
    N.B. YOU ARE STILL USING FOREIGN KEYS IN AN ASSOCIATIVE MAPPING

    Example data insert :
    Code:
    INSERT INTO drivers(name,license_number)
    VALUES
    ('Brian','JHsk3923ZX'),('Garry','Jalke933ds'),
    ('Phil','KJERejejd23'),('James','Xhheje552X');
    
    INSERT INTO cars(registration,make,model)
    VALUES
    ('XJS 235','vauxhall','nova'),('AKS 23KS','vauxhall','astra'),
    ('ZKA 775A','ford','focus'),('J321 JJA','ford','focus'),
    ('FJA 421K','chrysler','proton'),('LK82 JAA','chrysler','charger');
    
    /* Brian is allowed to driver all cars */
    INSERT INTO drivers_cars_map 
    SELECT d.id,c.id 
    FROM drivers d,cars c 
    WHERE d.Name='Brian';
    
    /* Garry is allowed to driver all vauxhalls */
    INSERT INTO drivers_car_map 
    SELECT d.id,c.id 
    FROM drivers d,cars c 
    WHERE d.Name='Garry' AND c.Make='vauxhall';
    
    /* Phil can drive all vauxhalls and fords */
    INSERT INTO drivers_cars_map
    SELECT d.id,c.id
    FROM drivers d,cars c
    WHERE d.Name='Phil' AND (c.Make='vauxhall' OR c.Make='ford');
    
    /* James can drive cars with particular registation numbers */
    INSERT INTO drivers_cars_map
    SELECT d.id,c.id
    FROM drivers d,cars c
    WHERE d.Name='James' AND 
    (
    c.registration='XJS 235' 
    OR c.registration='ZKA 775A' 
    OR c.registration='LK82 JAA'
    );
    Last edited by aschk; 04-20-07 at 10:37.

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    SQL statements for the many-to-many example :

    Who is allowed to drive a vauxhall ?
    Code:
    SELECT name FROM cars c
    JOIN drivers_cars_map dcm ON dcm.car_id=c.id
    JOIN drivers d ON dcm.driver_id=d.id
    WHERE make='vauxhall'
    GROUP BY name;
    I rewrote the above query in a different way (because the above has a bad use of joins)
    Code:
    SELECT name FROM drivers d
    WHERE d.id IN
    (SELECT driver_id
     FROM drivers_cars_map dcm
     JOIN cars c ON dcm.car_id=c.id
     WHERE c.make='vauxhall' AND c.model='astra'
    )

    Actually having used EXPLAIN on both those queries i'm unsure now. I need to think about how it works when the data scales. (btw the first query appears to be more efficient).
    Last edited by aschk; 04-20-07 at 06:45.

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Where's rudy when you need him?
    Last edited by aschk; 04-20-07 at 06:52.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by aschk
    Note in this example above there is NO NEED to have an associative table because a book can ONLY have one author!
    not sure that you can hold one book has one author
    You can't even assert that one book has one publisher

    an author may write many books
    a book may be written by many authors, or at least most of the systems books I have here, and many of the acadmeic books I had to study at Uni had multiple authors.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by aschk
    Where's rudy when you need him?
    watching with fond feelings for your fine examples

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    healdem : I was using it as an example rather than anything else. You are probably correct, in real life a book can have more than one author, but I needed to find something that was tangible to express the point. I did state it wasn't a real life modelled situation in my first post
    Last edited by aschk; 04-20-07 at 10:43.

Posting Permissions

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