SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    many to many update

    There is composite primary key on these two columns of MechWork:

    MechWork
    MechID | WorkID
    How can I keep track of updates for this table, where updates from users can be monitored prior to being updated? Is this feasible or should I just consider preserving the live WorkId and request for update of it only?

    Please note: there are only 4 row entries that any user can have. I want to review the updates before they are posted. (users may only have 2-3 rows, and later convert this to more; 4 rows; or less; 1 row, where users don't have the max. # rows a dummy value of '0' will be inserted )

    Will this do the trick:?

    MechWork
    MechID | WorkID | WorkID2 | Status | DateR | DateA

    To preserve the last live entry, where WorkID2 is the latest request with its status ('0' or '1') and DateR is the date of latest request and DateA is latest review date by admin.

    However this table with its 4 max values (per user) is only one table. There is another on the same site where users can choose up to 300 values, and is updatable. I don't know how practical keeping a history on this table might be especially with tens of thousands of registrants.

    I get the impression that many-to-many tables generally don't have additionaly linked tables created, like for DateR and DateA above. This right?

    if using the timestamp function, I would need one timestamp preceeding the others since the first is updated automatically by mysql each time the row is accessed - through I don't know about adding a counter to this row since the row itself would be updatable/more precisely, deleted and inserted by the application I use. So any counter would have to go somewhere else associating MechID | WorkID

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you please show a few rows of sample data, and show them going through their changes

    you ask "will this do the trick" but you haven't really explained what you want to do other than "keep track of updates for this table"

    i'm pretty certain a second WorkID is probably wrong

    if a mechanic does a particular type of work, the (composite) primary key records that fact

    are you suggesting by "keep track up updates" that you are never sure what type of work a mechanic does?

    also, please note, if MechID/WorkID is the primary key, you'll only be allowed one row with each pair of values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MechWork
    MechID | WorkID | WorkID2 | Status | DateR | DateA

    sample values: Here a user has entered one catagory value WorkID to show one type of work he or she performs (29).

    So the initial insert, of:
    // '0' by default is entered in WorkId2, and "1" in Status.
    4 |29|0|1| 0000-00-00|0000-00-00

    Lets say the intial insert is reviewed on the 27th and approved:
    4 |29|0|0| 0000-00-00|2004-02-27

    Next month the user decides to request an update for this category (35):
    (remember there are 6 total categories, and this probably wouldn't happen unless some of the 6 were originally left unselected)
    4 |29|35|1| 2004-03-27|2004-02-27

    Then admin reviews and approves(on 28th):
    //& transfers new requested category of '35' over to live col WorkID
    4 |35|0|0| 2004-03-27|2004-03-28

    OR

    The admin reviews and denies (on 28th):
    4 |29|35|0| 2004-02-27|2004-02-28

    All the while MechID | WorkID would remain the composite primary index.
    The above is intended to keep one category live until another is manually reviewed to see if it is fitting for the registrant.

    are you suggesting by "keep track up updates" that you are never sure what type of work a mechanic does?
    In this case the mechanics perform many different possible types of work. Are they acura certified? Do they rebuild diesels, etc. Extend little creative license, please. I'm working with a couple hundred possible categories and from experience (though very few) some registrants may make outlandish claims. I just want to monitor this first.

    An obstacle I see is that the application I use performs a delete -> insert (at least on the column but maybe on the entire many-to-many row. no small detail. So I don't think I can do this with a many to many update, however, maybe by way of regular update on the column.
    Last edited by datadriven; Feb 27, 2005 at 17:01.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, i think i understand now

    yes, it will work, although the problem of the unique primary key still exists -- each MechID/WorkID combination can exist only once

    so if you want to change 29 to 35, there had better not already be a 35 in there for the same MechID

    but the status and date manipulation should be okay
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks r937,
    so if you want to change 29 to 35, there had better not already be a 35 in there for the same MechID
    If there is will it just error out? Do you think javascript could be used to check that each value is unique?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, primary keys are unique by definition, the attempted UPDATE of 29 to 35 will fail with a specific error code (which one, i have no idea)

    javascript cannot access the database (unless you are talking about this new "ajax" magic -- see http://www.adaptivepath.com/publicat...ves/000385.php -- but this is *way* over my head)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Almost makes me want to put the values in repeating rows of a regular table.


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
  •