How to implement Draft functionality, with attached files?

Question

Should allow for:

1. User can save an entity as a draft, that would normally not be valid in its table (ie some missing values)
2. User can attach files to the draft
3. User can find, sort, and discard their drafts

This is not a homework question. I have some ideas, but wanted to see what is out there first. Thanks!

Answer 1

For the drafts side, what we do (LedgerSMB. PostgreSQL) is use a bool "approved" column or some approval information (where NULL means draft, for example "approved_by" or "approved_at").

I don't know the quirks of MySQL check constraints too well, but in PostgreSQL you can condition check constraints on the value in a field, something like: CHECK (approved is not true or title is not null). This makes it impossible to approve something that has not had everything filled on. We don't do this generally because approval is supposed to be all or nothing, but there are a few areas where something like this is done.

Answer 2

To save a record that would not normally be valid, you could try to change your definition of "valid". Or save it to a separate "drafts" table that has more relaxed constraints than the main table. To save "files", just store them in a BLOB. Probably best to do that in a separate table that has a key referncing the draft they are associated with.