editing/saving records transactionally

[CoachMan]

Not sure if my title makes sense or not :).  I'm building a nutritionist meal planning program for use with my coaches/clients.  Currently I have it set up so that if the user selects a meal plan, and then clicks 'edit', it imports the meal plan's foods into another table and then opens a card window for editing.  Once they close the card window and select the 'save changes' option, it then imports the records back to the original table.  This seems to be working fine, but it did require a lot of new table occurances (there are tables for foods, food weights (various units of measure/their gram weight), nutrient definitions, nutrient data, meals (recipes), and a few others).  Is this the best way of doing this?  Or is there a way where the data stays in the original table, perhaps by simply duplicating the records and then deleting the old ones if the user opts to save the changes?

[Steve Martino]

If you want the user to take your basic meal plan and forever modify it, that's fine.

But not the best way if you want to keep the original meal plans.  

If you want to keep the original meal plans, you would need a join table between the client and meal plan.  The user can still go into meal plans and modify if they feel they're new and improved meal plan is the standard they now want to always use, but I'm not a fan of destroying original and historic data.  

It's a typical model you can find on the internet for invoicing, with :  Customer---<Invoice----<LineItems>---Products.

[CoachMan]

17 minutes ago, Steve Martino said:

If you want the user to take your basic meal plan and forever modify it, that's fine.

But not the best way if you want to keep the original meal plans.  

If you want to keep the original meal plans, you would need a join table between the client and meal plan.  The user can still go into meal plans and modify if they feel they're new and improved meal plan is the standard they now want to always use, but I'm not a fan of destroying original and historic data.  

It's a typical model you can find on the internet for invoicing, with :  Customer---<Invoice----<LineItems>---Products.

I think that's how I have it set up now.  The user (coach in this case) can choose to copy a meal plan and then edit, thus not making any changes to the original meal plan.  Or they can edit the original meal plan (e.g., if they made a minor mistake, or just hadn't finished it yet).  Generally when I make client-requested revisions to a meal plan, I copy it and edit vs just editing the original.

My question is whether it's necessary/best practice to import the meal plan foods to a separate table for editing and then import them back to the original table (if user opts to save changes), or if there's a better way.  The idea is to be able to either save or delete changes all at once vs committing each record each time it's modified.

[Wim Decorte]

There are ways to do things transactionally without requiring moving the data first.  Also factor in the question whether it is possible for two coaches to modify the same meal plan at the same time, if so then you'll need to figure out how to do conflict resolution at the time the data is pushed back.  If you can stay closer to the native FM record locking mechanism then you'll not have to worry about that.

[CoachMan]

36 minutes ago, Wim Decorte said:

There are ways to do things transactionally without requiring moving the data first.  Also factor in the question whether it is possible for two coaches to modify the same meal plan at the same time, if so then you'll need to figure out how to do conflict resolution at the time the data is pushed back.  If you can stay closer to the native FM record locking mechanism then you'll not have to worry about that.

There won't ever be two coaches modifying the same meal plan...I'll have it set up so the coaches who work for me can only modify their own clients' plans, foods, etc.  They would need to make a copy of an existing food, meal plan, recipe, etc if they want to edit it...unless it's one they created themselves.