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 history- POST MOVED

    -----------------POST MOVED TO MYSQL FORUM ------------------------

    Alright, with a composite primary key on these two columns:

    MechWork
    MechID | WorkID

    How can I keep track of updates for this table, where updates from users can be monitored prior to being updated?

    a) There are only 4 row entires that any user can have. I want to review the updates before they are posted. (please consider scenario where users could only have 2-3 rows, and later convert this to more; 4 rows, or less; 1 row )

    b) Do I need 4 rows inserted originally for users to later make updates that can total 4 rows?
    Last edited by datadriven; Feb 27, 2005 at 13:38.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    updates? what updates?

    with only the primary key in the table (even if it's composite), you should not need to do an update, everything should be delete and insert

    which 4 rows are you talking about?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First: Pending.
    You need to have a new field in your table called something like Status. If the status is 0, then the change has not been committed (that is, this row doesn't count towards the maximum of four).

    For moderating the pending changes, you just select the rows specifiying status = 0


    To ensure that you only have four rows which have been approced you need to use a CHECK constraint, which uses a Function to actually do the testing:

    Code:
      use MyDatabase
      create table tblTest(
      	MechId int Not NUll,
      	WorkID int Not Null,
      	Status int Not Null Default 0
      	primary key (MechId, WorkId)
      )
      go
      
      CREATE FUNCTION fn_Testvals(@MechId int, @WorkId int, @Status int)
      RETURNS int
      AS
      BEGIN
      	
      	declare @ret int
      	select @ret = 0
      	if @Status = 1 
      	BEGIN
      	   select @ret = count(*)-4 from tblTest
      	   where MechId = 20 and
      				   Status=1
      	END
      
      	return @ret
      END
      GO
      
      ALTER TABLE tblTest
        ADD CONSTRAINT chk_vals
      	CHECK(Status > dbo.fn_Testvals(MechId, WorkId, Status))
      go
      
      insert tblTest values (10,20,1)
      insert tblTest values (10,30,1)
      insert tblTest values (10,40,1)
      insert tblTest values (10,50,1)
      insert tblTest values (20,20,0)
      insert tblTest values (20,30,0)
      insert tblTest values (20,40,1)
      insert tblTest values (20,50,1)
      insert tblTest values (20,60,1)
      insert tblTest values (20,70,1)
      insert tblTest values (20,80,1)
      
      select * from tblTest
      drop table tblTest
      drop function fn_Testvals

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oops, I really ought to test my code. Here is the working version

    Code:
     use MyDB
     create table tblTest(
     	MechId int Not NUll,
     	WorkID int Not Null,
     	Status int Not Null Default 0
     	primary key (MechId, WorkId, Status)
     )
     go
     
     CREATE FUNCTION fn_Testvals(@MechId int, @WorkId int, @Status int)
     RETURNS int
     AS
     BEGIN	
     	declare @ret int
     	select @ret = -1
     	if @Status = 1 
     	BEGIN
     	   select @ret = count(*)-4 from tblTest
     	   where MechId = @MechId and
     				   Status=1
     	END
     	return @ret
     END
     GO
     
     ALTER TABLE tblTest
       ADD CONSTRAINT chk_vals
     	CHECK(Status > dbo.fn_Testvals(MechId, WorkId, Status))
     go
     
     insert tblTest values (10,20,1)
     insert tblTest values (10,30,1)
     insert tblTest values (10,40,1)
     insert tblTest values (10,50,1)
     insert tblTest values (20,20,0)
     insert tblTest values (20,30,0)
     insert tblTest values (20,40,1)
     insert tblTest values (20,50,1)
     insert tblTest values (20,60,1)
     insert tblTest values (20,70,1)
     insert tblTest values (20,80,1)
     insert tblTest values (20,80,0)
     
     select * from tblTest
     drop table tblTest
     drop function fn_Testvals

  5. #5
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please keep in mind I'm still green at this.

    I could make into:

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

    This would only 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.

    This table with the 6 max values is only one table.

    There is another table on the same site where users can choose up to 300 values, and is updateable. I don't know how practical keeping a history on this table might be, with tens of thousands of registrants.

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

    As you may have guessed I've never done anything even like the check constraint/CREATE FUNCTION. Where does this actually go (run from a php admin page?) or is this somehow attached to mysql side?

    actually 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. the counter would have to go somewhere else associating MechID | WorkID

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you should have mentioned mysql in your very first post (in fact, you should probably have posted in the mysql forum)

    you will not be able to declare CHECK constraints, and functions only in version 5, which is still in beta status

    Quote Originally Posted by datadriven
    I get the impression that many-to-many tables generally don't have linked tables created, like for DateR and DateA above. Is this right?
    not sure -- what are you trying to do exactly?

    a many-to-many table is a linked table -- it has two foreign keys (even if you don't declare them as foreign keys, they do play that role)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As you may have guessed I've never done anything even like the check constraint/CREATE FUNCTION. Where does this actually go (run from a php admin page?) or is this somehow attached to mysql side?
    I thought you were using SQL Server. Maybe the MySQL forum would be the right place. The code I provided won't work for you, sorry.

    I don't really understand where you are taking your table with the example you show. The intersection table MechWork is supposed to be used only for showing that a relationship exists between Mechanic and Work, I extended this idea a little bit by adding Status, showing what status each relationship has. Then I added a check constraint to ensure that each mechanic has no more than 4 approved relationships with "Work".

    I think you will have to do all the heavy lifting in PHP, afaik mysql can't do this.


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
  •