Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm a programmer but also have worked as an archivist. As archivist it's a lot about keeping data.

I often get into arguments with colleagues when it comes to operations on data. I don't like the U and the D in CRUD too much. Rather then update a record I prefer to add a new one and have a reference to the old record. That way you build a history of changes. I also don't like deleting records but rather mark them as inactive.

Is there a term for this? Basically only creating and reading data? Are there examples of this approach?

share|improve this question
1  
Is there a term for this? Basically only creating and reading data? Sure there is: CR ;P –  Yannis Rizos Nov 16 '12 at 17:58
5  
From the user's point of view, that is still CRUD. I'm not aware of any specific labels for that style of implementation, but I think it is common in MANY applications. (Stack Exchange is a good example...) –  mehaase Nov 16 '12 at 18:49
    
You may want to watch a talk called The Impedance Mismatch is Our Fault. –  Anton Barkovsky Nov 16 '12 at 19:09
    
+1 At some point, someone is going to want reports and it's very hard to create reports on data that does not exist because it was "updated" out of existence. I find your approach very forward thinking. –  Chuck Conway Nov 16 '12 at 19:48
2  
You may also want to watch a talk about Datomic: infoq.com/presentations/The-Design-of-Datomic –  Marjan Venema Nov 16 '12 at 19:51
add comment

7 Answers

up vote 12 down vote accepted

Marking a record as deleted is known as soft-deleting. I've never heard an alternate phrase for updating, but I guess that's cause you soft-delete the old record and create a new one.

It should be noted, this is a controversial technique. See links: Con vs Pro.

share|improve this answer
add comment

One of the problems with retaining a change history is that it clutters up the database and can dramatically increase its size (depending on usage patterns). So a good idea would be to store the audit trail in a separate place, and keep the actual application tables populated only with relevant data. So each time a CRUD operation is performed by the application, the change is recorded in the audit tables, and the CRUD operation is performed on the application tables (no soft deletes).

By keeping the audit trail separate gives you a pristine data store for your application to interact with, while still retaining the change history should you need it. You can also now archive the audit trail separately or even destroy it, depending on your business requirements.

share|improve this answer
3  
This. Also, referential integrity becomes a nightmare; you can't specify a foreign key to "the one record in this table with this not-unique key that is not marked deleted". You get around that by persisting the data for the copy of a record that is about to be updated into a different table, before updating this "working copy" with the new data, but you still have massive data bloat to deal with. –  KeithS Nov 16 '12 at 19:06
add comment

EventSourcing sounds like the pattern that you may be looking for.

Let's take an example using a simple "car" object that we would want to keep track of the color of (pseudo C# code follows).

public class Car {
  public string Color { get; set; }
  public Car() { this.Color = "Blue"; }
}

With a CRUD implementation when we update the color of the car the previous color would be lost.

MyCar.Color = "Red";
MyCar.Save();  // Persist the update to the database and lose the previous data

This loss of information sounds to me like what you would like to avoid the most (hence the dislike for the update and delete part of the CRUD pattern).

If we were to rewrite the car class to instead respond to events when updating its change it may look like this:

public class Car {
    public string Color { get; private set; } // Cannot be set from outside the class

    public void ApplyEvent(CarColorChangedEvent e) {
      this.Color = e.Color;
    }
}

Now how would we update the color of this object? We could create a CarColorChanged event!

var evnt = new CarColorChangedEvent("Red");
MyEventStore.save(evnt);
MyCar.ApplyEvent(evnt);

Notice the lack of a save on the actual model object? That's because instead of persisting the model directly we persist the events that put the model to the current state. These events should be immutable.

Now's let's fast forward and change the color a few more times:

var evnt = new CarColorChangedEvent("Green");
MyEventStore.save(evnt);
MyCar.ApplyEvent(evnt);

var evnt = new CarColorChangedEvent("Purple");
MyEventStore.save(evnt);
MyCar.ApplyEvent(evnt);

If we were to look at our event storage (could be a relation database, file based, etc..) we would see a series of events relating to our car object:

CarColorChangedEvent => Red
CarColorChangedEvent => Green
CarColorChangedEvent => Purple

If we wanted to rebuild that car object we could do so simply by creating a new car object and applying the events from our eventstore to said object.

var MyCar = new Car();
var events = MyDatabase.SelectEventsForCar("CarIdentifierHere");
foreach(var e in events) {
  MyCar.ApplyEvent(e);
}
Console.WriteLine(MyCar.Color); // Purple

With the stream of events we can roll back the state of the car to a previous time period simply by creating a new car object and only apply the events that we want:

var MyCar = new Car();
var event = MyDatabase.GetFirstEventForCar("CarIdentifierHere");
MyCar.ApplyEvent(e);
Console.WriteLine(MyCar.Color); // Red
share|improve this answer
add comment

Event Sourcing is the way to go, and you should take a look at what Greg Young has to say about that.

http://goodenoughsoftware.net/

Also take a look at this presentation on his Database (Event Store). You can find other videos as well.

http://oredev.org/2012/sessions/a-deep-look-into-the-event-store

I wouldn't go for the "soft-deletes" answer unless you specifically need to be able to search for deleted items, but then you shouldn't think of them as deleted but rather archived. I think terminology is quite important here.

I would not want to maintain a "version table" either. All the "version tables" I have ever seen (including the on I am trying to clean up at the moment - 7 years of data corrupted because of bugs... and no way to get that back even though we have historical data... because that is just as corrupt) end up corrupted due to bugs in code and in the end you still lose data because you can't ever go back and recreate the data that the corruption messed up.

With the event sourcing model this isn't the case. You can always replay exactly what the user did. This is the very important difference between CRUD and Event Sourcing. The Event Sourcing architecture saves events in an event store, and not data objects or domain model objects. An event could easily impact multiple objects. Just think of a shopping cart solution where you convert each item in the shopping cart into an actual order. One event impacts all the item objects as well as the shopping cart objects, which is transformed into an order object.

If you kept a versioned copy of each row in every table in the database, then imagine the horror of having to rewind to a specific timestamp, not to mention the insane amount of space and performance overhead in maintaining that version table.

With Event Sourcing, you can easily rewind, by just replaying events until a certain point in time. Fast forwards can be put in place by using snapshots, but that's all a matter of implementation.

But the real advantage that I think you will like, seing as you are particularly interested in not losing data, is that if you discover a bug in the code that saves this data, then you don't need to go back and clean the data (which is often impossible because data is almost never complete). Instead just fix the bug, and replay all the events. Then you will have a database with nice correct data.

In case of debugging, how often have you asked the user to tell you what they did... why not just replay what they did and then stepping through the code! Pretty nifty huh.

Hope this helps.

share|improve this answer
add comment

You can use "soft-deletes" as pdr suggested. As for updates, you could keep history of records, sort of like my answer here: http://dba.stackexchange.com/questions/28195/save-history-editable-data-rdbms/28201#28201 where the OP wanted to be able to keep track of all versions of certain kinds of data.

share|improve this answer
add comment

Not precisely your example, but in older financial systems you had WORM storage. If you needed to "update" you wrote a new record and you always referred to the last record as current but no committed data could ever be overwritten.

A lot of folks carried that idea over into later systems. I have heard what you are describing referred to as WORM tables, but only in those circles.

share|improve this answer
add comment

basically crud cannot be completed without these 4 things . Crud means Create,Read,Update and delete so when you are only trying to read data you can use simple query for that but all these three things are attached to one and other and simple concepts of database

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.