In a small (5kloc) and algorithmically (or organisationally) simple
app what is probably the best option?

5kloc may seem small, yet if you have to maintain it over time, you will benefit from a clear design and well structured code (read: multiple classes or modules, each one focused to a particular task). This said – without knowing more about your application – my general advice is this:

1. always strive to factor out persistence from your algorithm code (aka “domain logic”). This keeps your algorithm code clean and simple, and makes the persistence logic easier to maintain. Mixing persistence with algorithm code introduces complexity that will be hard to maintain in the long-run.

2. implement persistence such that it is well encapsulated. Ideally your domain logic (and all its classes/objects) does not know that it is persisted.

3. Use a persistence framework (e.g. ORM) if possible, this greatly simplifies the design and reduces the need to write specific code (i.e. SQL, object mapping etc.).

4. If there are a large number of classes, group them by “inner cohesion”, i.e. figure out which classes belong to each other, and organize persistence by group.

In other words, don’t centralize all of the persistence logic in one large Database class, but rather centralize by group of classes. This keeps the system design tractable and your code readable.

1. The code is very readable – you know exactly what you persist.

2. This reminds me of the Memento-Pattern. It is good to revert changes.

3. It is like 1. but for inheritence – the baseclass defines the interface. Can cause problems if you have or do not have multiple inheritence.

4. Databases can not save every object but only some with constraints. You care about what the object consists of.

5. Gemstone/S and MagLev enables to store any object. In contrast to 1-4 it uses transactions. At the end of a transaction all modified objects are persisted.

Examples

In Python and marshalling-aproaches: persistence often uses 1. to convert an object to something persistable and 3 to not implement this algorithm too often. A Marshaller like in 4. enables object-identity in persistence and recursion. (persisting an object twice creates a reference).

The Webframework Rails in Ruby use 4. and 3. to store the model of the Model-View-Controller Program. You work on copies of the original object. You have like typed object attributes.

How to start

You can begin with 1. and a toString() method.

Once you need the same algorithm again, you can use 3..

If you need to persist recursive structures or ensure object-identity introduce a Marshaller that handles that. (It is also responsable for objects whose persistence behaviour you cannot touch.)

If you have many simple objects and do not worry about identity but just about some data: use a database 4..

2. seems to be unralated and can be built in every solution.

5. if your language supports it. Use what was given.

My first answer would be to save yourself the trouble and find a good data mapper ORM and focus on your business objects and logic, and let the ORM worry about persistence. The “data mapper” part is important here, because it alows you to have a domain layer which is completely separated from the persistence. So you’ll have just simple classes without any persistence leaking in in the form of a base class, or interface or anything.

Now if you still want to try and build your own, this is how I’d do it:

This is heavily based on how the Doctrine2 ORM for PHP works:
The entry point for whatever code is using the ORM is your EntityManager, this is what you use to fetch and save your entities/domain objects. Then you have your data mapping layer, which should be able to be injected with mapping info (in xml or whatever), so it can figure out how to connect tables to classes and columns to properties, and how to deal with relationships.
Underneath all that, you have the actuall db access layer, which has to be flexible enough to deal with different databases. So when you ask for an entity from the EM, it will go through the mapper layer to find out what table to use, then to the dbal to fetch the data, and again through the mapper to map the data to an object. When you save an entity, the EM will use the mapping layer to find out the table/columns from the object/properties, and send the data to the dbal to actually save it.
Doctrine also uses auto-generated proxy classes for your objects. These proxies are injected with the EM to allow for loading of relationships.

This is a largely simplified version of things, and I’m sure someone has already done it in whatever language you’re using. Doctrine for instance also has an ODM, for MongoDB (this is still beta).

EDIT:
You’ll notice that I never mentioned anything like a base class or an interface that you’d use in your domain objects. They should be absolutely stupid when it comes to persistence, and the only “leakage” from the persistence to the domain layers happens in the proxy classes, which should look exactly like your domain objects to the application that’s using them. This way your domain code stays clean and simple.