Can ORM skinned over bad database design reduce its effect to application?

Question

I'm working in mid-size .NET application which has a very very bad if not worst database design, almost of tables don't do normalization and complex table design.

E.g. I have one table to keep track of stock movement and every times I modify this table I need to update current stock table. In my opinion this is a bad design, because I can use SUM when I want to know current stock.

Another example, Every time I insert new stock movement data, I don't insert to stock movement table directly, instead I insert data to temp stock movement and then I use tricker to copy that data to stock movement and other relate tables (may be update customer credit).

I try to adopt ORM with this application but its seem too hard for me, I need a couple of hour to create mapping-class and a couple day for test its, because not-normalize and vague data persistence.

I want to know, Can ORM reduce problem from bad database design like this? If yes, how can I do that?

P.S. I can't change database design at all, because almost of members in my team have no idea about database concept.

Answer 1

I don't think so. ORM and objects aren't magic. If you put them on top of a bad design, it's still a bad design.

If it's not imperative that every stock update appear right away, you can queue them up and do "write behind" in an asynchronous way. But that's just papering over a potential problem.

Answer 2

I would recommend that you try to build an image of a corrected database design in the data layer itself. You didn't say which database you're using, but if you're using SQL Server, one technique you can try to to build normalized views with INSTEAD OF UPDATE and INSTEAD OF INSERT triggers. When you write new code, target the normalized views instead of the legacy tables. Over time you can migrate your legacy code away from the poorly design tables and replace them on a case-by-case basis. Eventually you will have a well designed database.

One important note is that, while normalization is an important consideration, you should also be looking at the entities in your system to make sure that the conceptual model of the databse is correct. In general, a database that is correct at the conceptual level will also conform to the 1st 3 normal forms, but the converse is not true (normal forms don't guarantee conceptual correctness). If you're going to undertake this work, you want to make sure you're moving to the right design for your system.

It is definitely a good idea to use an ORM for all new database access code. This gives you a lot of flexibility going forward. So the general steps for writing new features is:

1. Create a facade within the database that presents a proper data model using views or, if possible, by restructuring the tables.
2. Write your ORM code agains the proper data model.
3. Always look for opportunities to evolve your data model out of it's current rut.

Your ultimate goal should be to refactor the database. You may wish to familiarize yourself with the work of Scott Amber on this topic.

Answer 3

And it may not be bad design. There are times when items are precalculated so that reporting can run without timing out. Just because you can sum one stock when you need to see it on the user interface quickly doesn't mean that someone else running a report that summarizes all stocks wouldn't have a difficulty doing that in a timely fashion. It is often better to pre-calulate some things. Sometimes doing a calculation once is better than redoing it every time it is needed.

Not knowing your whole database design, I can't say if this is a good or bad idea in your specific case, I just point out that there are many times when this is a good design.

There are reasons why the design is this way, try to find them out before you decide it is a bad design. You still may find that there is bad design, I'm just saying don't assume it is until you understnd the reason why just because it doen't fit your personal preference.

If you truly want to redesign the database (and have the go-ahead from amangement), then read about refactoring databases (There are good books on this) before you try to do anything. There are things that need to be considered that application developers are not usually aware of.

Answer 4

In general: no. As the others have pointed out: An ORM is only a tool to reflect the database state and design inside you application, adapted to your programming language.

In this case: probably yes. If you build a rich domain model, the method alterStockPortfolio(StockMovement s) could update (i.e. call appropriate methods on) the related entities. Add method calls one by one as you remove the corresponding database triggers. After some time you have an application where the business logic is in the program code, where you can manage it with unit tests. (start by writing those).

Pre-computing derived values like this is often necessary in order to overcome performance bottlenecks. It is not necessarily bad design. Still, the logic that controls the values persisted in the database should only be in one place, otherwise you lose control over it with seemingly random changes happening "in the backgound". In my opinion that one place should be the application code.