Client sends you a word doc with data on it about the widget you want to build. You save said word doc. Two days later, they change it, and send you a new version. Over the weekend, they realized that they need to scrap two items on the second revision, so they trash that and send you a new version.
Now, I have three word docs. And, presumably, I'll have a dozen more before they are done sending me their stuff on the widget they want built.
So, I want to keep track of these in a database. I have two tables, one is "opportunity" (because that's what "Can you build me this?" is until they write a check), and the other is opportunity_uploads.
My current thought is to add a column called: "opportunity_uploads_repla
ces", which would contain the opportunity_uploads_id of the previous version of the document.
Then, to find the most current version of this document, I can just sort DESC based on this "replaces" column.
But what if I want to see and download all versions? Would I just do a series of joins and join the same column? The relationship is only from opportunity_uploads_replac
es -> opportunity_uploads_id. If opportunity_uploads_replac
es IS NULL, then it is considered to be the first version. Else, it replaces opportunity_uploads_id.
Is there a better (more elegant) way to do this?