R – Draft version of database table

databasedatabase-designdesign-patterns

I have a database with a report table. This table represents report data, and has constraints.
Now the application is used by firemen, and they might have to run off and put out some fires.
So in the middle of filling out data for this table, they might have to run off.
What we do is when the session run out we save the data to a draft table, where we simply serialize the object graph, no constraints (since we can't know if everything is in a valid state).
And when the fireman later wants to pull data back out we simply pull out the draft again.

This works fine!
However the firemen now wants to be able to search the table and the draft table.
So here I would like to maybe store all the data (including the draft) into one table, that I search, so I don't have to consolidate the search from two tables..

Is there some kind of database pattern for drafts?
What I want is a patternfor storing data without constraints (in draft mode) and with constraints (in final mode).

Best Answer

Go with the simple solution: create a View which is just a union of the two tables (which should be fairly straight forward as they I assume they both have (almost) identical structures) and then run searches on that as the source if they want to include both.

I wouldn't actually merge the complete and draft data at any point: the potential for error and accidental use of unvalidated data seems huge.

You wouldn't be working on an interface for the IRS in the UK at the moment, would you? If not, sounds like DK have gone for a similar (in principle) solution to this problem (I used to work for a UK Fire and Rescue Service).

Related Solutions

Mysql – How to quickly rename a MySQL database (change schema name)

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

RENAME TABLE old_db.table TO new_db.table;

You will need to adjust the permissions after that.

For scripting in a shell, you can use either of the following:

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;

Notes:

There is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.
If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:

mysqldump old_db | mysql new_db
If you have stored procedures, you can copy them afterwards:

mysqldump -R old_db | mysql new_db

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

There are a few steps to see the tables in an SQLite database:

List the tables in your database:
```
.tables
```
List how the table looks:
```
.schema tablename
```
Print the entire table:
```
SELECT * FROM tablename;
```
List all of the available SQLite prompt commands:
```
.help
```

Best Answer

Related Solutions

Mysql – How to quickly rename a MySQL database (change schema name)

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

Related Topic