SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot Fiska's Avatar
    Join Date
    Jul 2004
    Location
    Dardania - Ancient Illyria
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Schema design, I need an advice

    Hello Folks,

    my issue is a schema design, I have already one schema design but I don't if it is correct, or there is a possibility for a better one (although there is always possbility for smth better). So Im using mysql with innodb engine as I need some foreign key contraints as you will see bellow.

    So basically I have one main table call it T1(id, parent, entity) and then every entity (which must be unique) has to be associated to all other existing entities in T1 (excluding itself), to do this I simply created another table call it T2(parent1, parent2, val), meaning that I have parent which is one of the entries from T1 (therefore parent1 has FK to T1->id) it basically just holds an ID from T1, same does parent2, but for parent1, we have all other id's in T1 enlisted as parent2, and an association value.

    The problem is now, I need to create a trigger, so whenever one inserts an entry in T1, then automaticall insert all associations in T2 for that entry in T1, this way each entry in T1 is associated with all other entries in T1.

    1. Do you think that there can be a better DB design?
    2. Can you help me write this trigger that does this automatic entries?

    I've already written a trigger, but it inserts only a single value, what I need though is, a trigger that for one entry in T1, it will insert associations with all other entries in t1, which will be stored in table t2.
    let's make things easier

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Fiska View Post
    every entity (which must be unique) has to be associated to all other existing entities in T1 (excluding itself)
    you don't need to store this

    any time you need to use the relationships, just cross join the table with itself (and use a WHERE clause to eliminate those rows where an entity is matched with itself)

    simple, clean, minimal data, no trigger necessary

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot Fiska's Avatar
    Join Date
    Jul 2004
    Location
    Dardania - Ancient Illyria
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you for your reply, but can you be more specific? If i don't store it, what happens with association value? As I need association value, which will be a criteria later-on for querying the db.

    thank you
    let's make things easier

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

    okay, you need to store all cross-references

    so you need one insert for T1, and another insert for T2, and you were hoping to have a trigger for this second insert

    what's wrong with just using this:
    Code:
    INSERT
      INTO T2
    SELECT @parent, id, @defaultvalue
      FROM T1
    two inserts, granted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot Fiska's Avatar
    Join Date
    Jul 2004
    Location
    Dardania - Ancient Illyria
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, I like the idea, sounds elegant, but what your query would do is just replicate my table t1 into t2, and what I want is: if I insert one row into T1, then in T2 I would need all other rows in T1 associated with this new row, meaning that for T2, the first attribute would contain the new row, and the 2nd attribute would contain all other row in T1 associate with t2.

    thank you
    let's make things easier

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Fiska View Post
    ok, I like the idea, sounds elegant, but what your query would do is just replicate my table t1 into t2
    um, actually... no

    what my query does is exactly what you're asking here:
    what I want is: if I insert one row into T1, then in T2 I would need all other rows in T1 associated with this new row, meaning that for T2, the first attribute would contain the new row, and the 2nd attribute would contain all other row in T1 associate with t2.
    did you test it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot Fiska's Avatar
    Join Date
    Jul 2004
    Location
    Dardania - Ancient Illyria
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi,

    I was away for a while, so basically I couldn't try your example, so what does @ sign stand for?? And should I implement this as trigger or??

    thanx
    let's make things easier

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, let me walk you through it

    suppose you add item 57 to T1

    so the new row in T1 has id=57, and whatever parent it has (doesn't matter for this example), as well as an entity value (which also doesn't matter)

    now you need to add a bunch of rows to T2, which has 3 columns -- parent1, parent2, and val

    so parent1 on all these rows will be 57, parent2 on each row will be all the id values currently in T1, and val will be some default value

    so @parent is 57, @defaultvalue is whatever value you want to have for val, and the other parent2 column comes from the id in T1 --
    Code:
    INSERT
      INTO T2
    SELECT @parent, id, @defaultvalue
      FROM T1
    do you see how that works now? @parent and @defaultvalue were just placeholders in the query to hold the values that your application will supply
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot Fiska's Avatar
    Join Date
    Jul 2004
    Location
    Dardania - Ancient Illyria
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah great... I see now what you mean, I was confused with @ sign, I thought it has special meaning to triggers, therefore by using it mysql was reporting errors. Ok so this way basically I just insert this SQL statement directly from php, without need to add triggers.

    thanx a lot, nice solution.
    let's make things easier


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
  •