SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast greg606's Avatar
    Join Date
    Jan 2006
    Location
    Poland, Wrocław
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need help: innodb or simple joins?

    I'm designing a database with 3 tables:
    users
    hyperlinks
    tags
    Each table relates to another table with many-to-many relationship.
    (i.e.
    each user has many links, each link has many users
    each user has many tags, each tag has many users
    each link has many tags, each tag has many users)

    but -
    1. tags are optional
    2. I may be expanding this db to include e.g posts (which will also have tags)

    At the moment I don't use innodb as I follow Kevin Yanks explanations about
    joins and "connecting tables" but I consider using foreign keys what entails using inndb.

    How do you think I should design these db and should I use innodb and foreign keys?
    I read about foreign keys being used without the mention of innodb (when I just put the same columns in two tables)

    I will be grateful for any advice. Regards,
    Greg

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i don't understand the "or" part of your question -- you will be using joins to get your data out, no matter whether the tables are innodb or not

    innodb must be used if you want the database to enforce data integrity through foreign keys

    the foreign key columns will still exist in myisam tables except the database won't know which columns are foreign keys, only your application logic will know (and will have to enforce them)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast greg606's Avatar
    Join Date
    Jan 2006
    Location
    Poland, Wrocław
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    you will be using joins to get your data out, no matter whether the tables are innodb or not

    innodb must be used if you want the database to enforce data integrity through foreign keys
    Right. Could you explain what do you mean data integrity through foreign keys

    How would you design those relationships (avoiding empty cells)?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    data integrity through foreign keys means that you cannot relate a tag to a user that doesn't exist, you cannot relate a link to a tag that doesn't exist, and so on

    for an example of how to set up the tables, see this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast greg606's Avatar
    Join Date
    Jan 2006
    Location
    Poland, Wrocław
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let's say I have tag_id (autincrementing) and I want to use this key in another table. Is this possible? How can PHP possibly know the value of this key so it can put it to another table...?
    And can this be done in one query?
    Please help
    Regards,
    Greg

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    search the mysql site for MYSQL_INSERT_ID and LAST_INSERT_ID

    one query? no, you do an INSERT into tags (if it's not an existing tag) and then use the value of the auto_increment to do a subsequent INSERT into xxx_tags (where xxx_tags is the relationship table between tags and users or links as the case may be)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    if you're using php, the use mysql_insert_id() php function. if you instead use select last_insert_id() this will result in an extra call to the database. when you insert a row with mysql, the id of the last inserted row is returned with the result handle, so the id is already in php's memory space. if you do the select, php has to make another call to the mysql server to get the information. calling the php function will be faster.

  8. #8
    SitePoint Enthusiast greg606's Avatar
    Join Date
    Jan 2006
    Location
    Poland, Wrocław
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MISSION ACOMPLISHED!!!
    Thanks for explanations...
    thanks longneck. Indeed, I used PHP function: mysqli_insert_id - worked like magic. The data is in the db.

    Now I need to create a join to pull them up.
    Kevin Yank in his proverbial book explained the joins without using the join "function". I am a bit perplexed.
    Still I don't understand when to use innodb and its foreign keys but that's another topic.
    Here is my db:
    Code:
    CREATE TABLE links (
      link_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
      link_name VARCHAR(255) NULL,
      user_id Varchar(45) NULL,
      PRIMARY KEY(link_id)
    );
    
    CREATE TABLE tags (
      tag_id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      tag_name VARCHAR(255) NULL,
      user_id Varchar(45) NOT NULL,
      PRIMARY KEY(tag_id)
    );
    
    CREATE TABLE tags_links (
      tag_id INTEGER(10) UNSIGNED NOT NULL,
      link_id INTEGER(10) UNSIGNED NOT NULL,
      PRIMARY KEY(tag_id, link_id)
    );
    
    
    CREATE TABLE users (
      user_id Varchar(45) NOT NULL,
      mail Varchar(45) NULL,
      entry_date Varchar(45) NULL,
      user_password Varchar(45) NULL,
      PRIMARY KEY(user_id)
    );
    Questions:
    1. How should I formulate the query to get tags related to a link?
    2. Do the fields have proper types?

    Thanks again,
    Greg
    Last edited by greg606; Mar 12, 2006 at 19:44. Reason: added thanks to longneck

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    1. any basic sql tutorial (google is your friend)
    2. yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •