How to build a good API on top of a bad database?

Question

I have a badly designed database. It needs to stay that way for various reasons.

In my experience I have enjoyed ORMs like ActiveRecord, but have only ever used them for new projects without an existing database.

Is there a way to make a good data model using an ActiveRecord-style ORM without changing a badly designed database?

Answer 1

Others have touched on the technical side of this, so I want to add another view:

Stop listening to your binary feelings for a while, and try to focus on the real-world consequences of working with the current design. Exactly what are the problems you will experience if using it as a starting point?

• Will it make your project take longer to complete?
• Will it require more people?
• Does it require special competence to work with?
• Will it jeopardize data quality?
• Does it make maintenance harder?
• Will it have severe performance implications?
• What consequences will it have for the next project after yours?

and

• What needs to be done in order to move from current state to your desired state
• What is the cost for doing so
• What are the implications for your current project if you first have to fix the design?

Because in the end, this kind of stuff is really all that matters. If you can't "sell" the idea that the design is bad, it really isn't and you are just bitching over stuff that doesn't matter to anyone other than us fellow geeks who also live in the binary world where NOT(Good) = Bad.

Sure this bigint column could have been replaced with a tinyint, and those columns should have been moved to another table, and this piece of repeated logic could have been hidden behind some view/function, and this API will be slower than neccesary, but these are all crappy details that may or may not matter in the non-binary world.

I have a favourite table to hate at work. Approximately 1% of the data is inconsistent and just plain wrong. The cost for cleaning up this last 1% would be huge (consolidated data from multiple systems) and the errors don't even show up in the decimals when aggregated. In fact, it is me who have a problem. I can't add a particular constraint to the table. So instead I have to add a where predicate to the 2 programs using it. I've tried several times to make a case for fixing it, but nobody is willing to invest in something that isn't a problem. And I agree with them.

Answer 2

Questions like this have multiple good answers. Some better than other. Ideally I can think of two solution and both of them stem from the same idea. Decorator.

So if the database design is poor, then the best way to improve your code quality is to come up with the correct domain model and decorate a layer on top of your database to work correctly with your underlying data model.

The first way to do is:

1_ Most ORM allows a way to represent multiple tables in to a single entity and vice versa. But this solution is complex and fraught with danger.

2_ My preferred solution would be to use database de-normalization techniques like View, Materialized Views and Procedures to create a new layer on top of your data model and create a ORM on top of this layer. ( Preferably create a new schema and create view\mv on the owner schema. This way any application which use the older schema can continue to work and you have complete control on how you want to design your data access layer.).