Are there examples of non CRUD approaches?

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?

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.

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.

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

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.

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.

Yes its quite common in enterprise systems there are basically two approaches:-

“bi – temporal” in which every record has a valid from and valid to time stamp (the “current” record having a valid to date of “forever” – null, “9999-12-31” or some such high value).
Records are never deleted instead the “valid to” date is set to the current time and in the case of an update a new record is inserted with a valid from of the current time and a forever valid to date.
“history table” — every time a record is altered a copy of the old record is dumped in a history/log table with a timestamp for the event.

There are great variations in granularity for both approaches. e.g. If the quantity of widgets on an order item is changed do you maintain history for the entire order or just that one item?

Generally speaking “bi-temporal” is a lot of extra work and only worth it if you have a lot of use cases like “auditor gets order status as of 31st December”.

You can use “soft-deletes” as pdr suggested. As for updates, you could keep history of records, sort of like my answer here: https://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.

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

Filed under: softwareengineering - @ 18:00

Thẻ: crud, data, database-design, terminology

Thiết kế website giá rẻ

Danh mục

Are there examples of non CRUD approaches?