1

Lets say I'm building foursquare, users can edit venues and the changes will not take place immediately till an admin approve them,

I have a Table for Venues,

So for the functionality I explained above do I need another table Called may be 'ProposeEdits' to save the edits for a Venue and then when accepted mark them done ?

Viral Savaj
  • 3,379
  • 1
  • 26
  • 39
Sahan
  • 1,422
  • 2
  • 18
  • 33
  • Dude, Is there only single field which update like status or flag? Or some thing also update like some message field? – Shahzad Barkati Apr 30 '15 at 06:00
  • For related reading, check out "database record versioning". This (http://stackoverflow.com/questions/323065/how-to-version-control-a-record-in-a-database) discussion may give you some ideas. – Luke Apr 30 '15 at 06:01
  • Actually, better link: http://en.wikipedia.org/wiki/Slowly_changing_dimension Take a gander at that, let me know if anything jumps out, and I'll help out with implementation. The solution we consider probably depends on whether you want history, whether any other entities reference the venue, whether other entities will will refer to a particular version of the venue, whether edits are always accepted/should take immediate effect, how worried we are of performance (how many rows?). We'd probably look at something like Type 6, but with timestamps for write and accept. – Luke May 01 '15 at 03:31
  • @Luke I'm going with the Stackoverflow database structure, http://stackoverflow.com/questions/29963229/stackoverflow-database-design/29963251?noredirect=1#29963251 – Sahan May 01 '15 at 07:08
  • Looking at that schema, it would appear that there is a Post table (with id, title, body, etc), and a PostHistory table (with id, revision id (so as to group revision parts), revision type (initial title, initial body, edited title, edited body, closed, opened, etc), revision content). So whenever anything happens to any part of a Post (change, initial value on create, delete), the Post record is changed, and a record is added to the PostHistory table with the type and new content recorded. If you change multiple things (e.g. title, AND content), they can get the same revision id. – Luke May 05 '15 at 01:11
  • So is that something you want to go with? Keep in mind that our problem is slightly different, since we need to only apply changes on approval... I think it's reasonable to have a Venue (e.g. id, name, description) and VenueHistory (id, revision id, venue id, revision type, revision content, moderated date, moderation status) table. The moderated date and status are initially null. Any records in VenueHistory that haven't been moderated would be shown to an admin. Once moderated, then date is set to now, and status to true/'APPROVED'/whatever, and then change is applied to the Venue record. – Luke May 05 '15 at 01:17
  • Yeah, That sounds good Luke, Thanks – Sahan May 05 '15 at 05:10

2 Answers2

1

Absolutely No.

You Need To add just one column to the end of your table Venue called status.

And Make one Page in PHP for admin called AcceptVenue.php.

When user edits the Venue you just set status to No

And when admin opens AcceptVenue.php list all edits in tabular form with two buttons accept or reject.

When admin accepts and edit just update the status to YES else keep it unchanged.

I am not posting the code.First You try then I will help you if you face any problem.

Scenario:

Venue table : (id,name,venue,date,event,status-default=NO)

user edits the values but the Status Remains Unchanged

Admin opens the AcceptVenue.php List all records like this:

Name Venu    Date   Event   Action

xyz   zux  12/12/12 xyz   Accept Reject

Based on admin action run sql query

"update venue set status='YES' where id='venueId'"

Update 2 If you want to keep both versions:

proposedEditTable

   (id,name,venue,date,event,original_venue_id,status-default=NO)

When admin accepts the Edit You just need to run

  "update venue set name=new_name,date=new_data..... where id=original_venue_id"

original_venue_id keeps track of which row is being edited in venue table

Rahul
  • 5,594
  • 7
  • 38
  • 92
  • Where do you store the proposed new data? Where do you store the old data? You need both, such that you can overwrite with new, or revert to the old data. – Luke Apr 30 '15 at 06:01
  • Ok, so you've marked a record as either accepted or unaccepted. But you still need to store the proposed state and the preexisting state in two separate places, and then either override the preexisting state with the new state on accept or throw away the proposed state and keep the preexisting state if the changes are declined. – Luke Apr 30 '15 at 06:14
  • If you want to Keep Both State Then You Have to make two tables for sure. – Rahul Apr 30 '15 at 06:16
  • @Luke Thanks for getting involved, I have been taught never ever to discard data. NEVER :) So i need to have all the data in the database so that one day a admin can open up a page and see what changes users have done and who made them, Do you get what im after ? – Sahan Apr 30 '15 at 08:40
0
Table 1 : venues
Table 2 : temp_venues

event 1 : user edits the venue
step 1 : store the user id and venue in temp_venue
step 2 : show temp_venues entries to admins

event 2 : admin approves the venue
step 1 : copy row from Table temp_venues
step 2 : insert row in Table venues
step 3 : delete row from Table temp_venues
Hussain Akhtar Wahid 'Ghouri'
  • 6,527
  • 8
  • 50
  • 85
  • event 3: admin denies the changes: simply delete the proposed row from the temp_venues table! – Luke Apr 30 '15 at 06:07
  • @Luke, Have you ever edited a post in Facebook ? When you do that It shows that the record has been edited and can see what edits you have done thorough the time, This is exactly what im after – Sahan Apr 30 '15 at 08:45