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

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 开发者_如何学JAVAchanging a badly designed database?

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.

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.).

I rewrote your question as:

Can application programming interface having desirable or positive qualities especially those suitable for a thing specified for a database having undesirable or negative qualities be made?

So my answer for your database is:

• If negative qualities are negligible, then yes.
• If negative - and undesirable qualities can be ignored with extra work, then probably yes.
• If undesirable performance or behavior, then probably no.
• If database works, but only in best case scenario, then no.

Ponder this for a second. Really.

I have a house with moist problems. It needs to stay that way for various reasons.

In my experience I have enjoyed decoration like plastering and wallpaper, but have only ever used them in houses that didn't have moist problems.

Is there a way to create a house without moist problems using decorations like plastering and wallpaper, without changing the house-with-moist problems itself ?

It depends what terrible is in that database. If database has bad table and column names, bad columns databatypes or bad access layer (like stored procedures, views etc) you still may use your favorite ORM and map bad database to nice model. But if database doesn't match some normal form, it may cause problems with mapping to model.

The only thing I can think of, is that you could "rename" the database fields as your convenience (in order to have a consistent naming convention if it wasn't the case), since most ORMs allow that.

For the rest, do what you can on the DB schema: doing "soft changes" like adding new fields or indexes when appropriate, should not break existing code running on the DB (if it's the main reason why you can't change schema).

It needs to stay that way for various reasons.

I don't believe that, but it doesn't really matter. Managing this at the database level is really pretty simple. (But simple doesn't necessarily mean easy.)

Updatable views implement logical data independence. Build updatable views in such a way that application code can't tell whether it's reading and writing a view or a base table.

Those updatable views insulate application code from the structure of the base tables. Now you're free to fix the design. When you change the base tables, change the views to keep their behavior consistent.

As to whether you can use an ORM to make a good data model without changing a badly designed database, I'd say, "Don't count on it."