SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    1:1 how to ? - yes, only this. :s

    Hello all,

    This is a very basic question but I don't even know how to search for it.

    We have two tables:

    Images_Logo table - that will have the logo images and related information inside.

    Associations table - that will have the associations info like name, address..

    It makes no sense to store images specific info on the associations table.
    So how can we do a 1:1 relationship here?

    We do it at creating time? Or when we want to retrieve the information?

    Are we forced to use innodb or we can use myISAM, or, are we force to use foreign keys or can we use joins instead ?

    Regards,
    Márcio

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    foreign keys or joins is not an "either or" situation -- use foreign keys if you want the database to enforce relational integrity

    please explain why you think it makes "no sense" to store logo image information in the associations table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    Quote Originally Posted by r937 View Post
    foreign keys or joins is not an "either or" situation -- use foreign keys if you want the database to enforce relational integrity
    So that when we remove one the other gets removed or when we update one the other gets updates, or, when we try to remove the other doesn't let us doing it...

    Quote Originally Posted by r937 View Post
    please explain why you think it makes "no sense" to store logo image information in the associations table
    Because associated with the images storage we will have other informations like, filename, mime_type, file_size, file_data ... and those informations don't belong to Associations hence, they seem to be more related with Images or files, exclusively.

    Thanks,
    Marcio

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    excellent answer

    okay, to make a 1:1 relationship, put a foreign key into the logos table, to reference the associations table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok... on the last hours (yes hours! ) I've come up with something like this:

    Code MySQL:
    CREATE TABLE association
    (
        id_association INT not null PRIMARY KEY AUTO_INCREMENT,
        name_association varchar(255) not null,
        address_association text,
        post_cod varchar(50),
        telephone_association varchar(100),
        email_association varchar(100),
        url_association varchar(255),
        index (name_association)
    )
    CHARACTER SET utf8 COLLATE=utf8_general_ci,
    TYPE=InnoDB;
     
    create table image
    (
        id_image serial PRIMARY KEY,
        name_image varchar(255) not null,
        mimeType_image varchar(255) not null,
        fileSize_image int not null,
        fileData_image longblob not null,
        id_association INT,
        FOREIGN KEY(id_association) REFERENCES association (id_association) on delete cascade,
        index (name_image)
    )
    CHARACTER SET utf8 COLLATE=utf8_general_ci,
    TYPE=InnoDB;

    Is this ok?

    If so, please advice:
    When we delete an image, the correspondent association should NOT be deleted. By taking no options on that, we are assuming this procedure?


    Thanks a lot,
    Márcio

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by oikram View Post
    When we delete an image, the correspondent association should NOT be deleted. By taking no options on that, we are assuming this procedure?
    you did it right

    in this relationship, the refencing table is the logos, and the referenced table is the associations

    deleting a referencing row is always possible, and never has any effect on the referenced row

    you can delete a logo if you wish, but the association will stay

    the ON DELETE and ON UPDATE options, which are specified in the referencing table, refer to delete/update actions taken on the referenced table

    so if you delete an association, or change an association's id number (although with an auto_increment, you would never need this), then you have options as to what to do to any related logos
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the ON DELETE and ON UPDATE options, which are specified in the referencing table, refer to delete/update actions taken on the referenced table
    Getting crazy:
    So this is like triggers (that I've heard so many times here and there) that are inside this innoDB engine?

    When we delete on table A, it deletes the corresponded record on table B,
    just by interpreting those lines:

    Code MySQL:
    DELETE FROM table A
    WHERE column = 'somecolumnvalue'
    ?

    I it's too absurd I will leave it for the future, np.


    Regards,
    Márcio

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yup, that's what ON DELETE CASCADE will do -- if you delete an association, its logo is also automatically deleted

    why wouldn't you want that?

    do you wish to have logos in your database for associations that don't exist?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yup, that's what ON DELETE CASCADE will do -- if you delete an association, its logo is also automatically deleted

    why wouldn't you want that?

    do you wish to have logos in your database for associations that don't exist?
    Not at all. I'm finding all this quite cool. Long live innoBD

    Thanks for your time once again
    Márcio

  10. #10
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry to repost here, but it is so related that I found useless to create a new post:

    Regarding this 1:1 relation here, I'm facing a hard time trying to understand how will the insert work here.

    At the same time we insert something on Associations a corresponded Image should be inserted into the Image table.

    On the logical part, I can have two inserts one after the other.
    The first puts the data into Associations and the second, inserts an Image.

    But... and those are my questions:
    What if one of the tables, for some reason, doesn't work - we could have something on the Associations table, and nothing on Image table, supposing the image one was the table that didn't work for some reason.

    What if two users, at different points, for a bizarre coincidence, are trying to insert data at the same time, then it could be the case that we have Two associations but with switched logos. :s

    What should we do on those cases?

    If those cases are very Very rare (I'm talking about a very little database here), then using one insert after another is the way to go?

    Update: Why do I need those inserts to happen almost at the same time?
    I'm assuming that, for retrieving the association and the correspond logo, I would join both tables where the id of one is equal to the fk of another, hence, when we insert (or delete) the id's should be synchronized.
    I'm not getting this right perhaps... :s

    Thanks a lot,
    Márcio
    Last edited by oikram; May 6, 2010 at 04:53. Reason: some clarifications made

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by oikram View Post
    What if two users, at different points, for a bizarre coincidence, are trying to insert data at the same time, then it could be the case that we have Two associations but with switched logos. :s
    No.
    You're using an autoincrement key field in your associations table. Assuming that you're using PHP, you'll use mysql_insert_id() to retrieve the ID generated for the AUTO_INCREMENT column by the previous query. It will give the id generated by the previous query in that session, not the last one generated by another session.
    I guess other languages will have a similar function.

  12. #12
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see.

    So in that session is something we should be aware at. ok.

    I will try to use this, since I'm on PDO:
    http://php.net/manual/en/pdo.lastinsertid.php


    Thanks a lot,
    Márcio

  13. #13
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Maybe session isn't the right word. If you read the php manual page I linked to, I'd say it is limited to a single MySQL connection.

  14. #14
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And perhaps we can even use mysql function and leave all to mySQL no?
    LAST_INSERT_ID()

    ?

    Thanks again,
    Márcio


Bookmarks

Posting Permissions

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