ORM - Does the Database Schema Drive the Entity Composition or Vice-Versa?

views:

136

answers:

+8 Q:

ORM - Does the Database Schema Drive the Entity Composition or Vice-Versa?

We've had quite a bit of discussion among our development group concerning whether the composition of entities should drive the database design, or should the database design drive the composition of the entities.

For those who have dealt with this, what has been your philosophy? Of course, not every entity maps 1:1 to a database table. But, for those that do, how have you handled this? IOW, which comes first, the database table and then a corresponding entity or an entity and then a database table to persist it?

Thanks.

I would say that you build your logical data model, and build the database and objects corresponding to that.

In fact, I would question the assumption that the database table and corresponding entities can't corresponding. I've rarely if ever seen a case where they really couldn't (if you are building an application from the ground up). Also, I would say that every time the object model and database schema diverged, it introduced a lot of problems.

I've come back around to the idea of that everything is simplier if you make them always match, however heretical that may be.

Mike Mooney 2010-02-14 02:43:21

I've yet to see a direct translation of a link (many-to-many) table. Generally, they are implemented as Vectors/ArrayLists attributes of the main object rather than their own class.

OMG Ponies 2010-02-14 02:50:02

@OMG Ponies: There's no direct translation of an association table because the association table isn't a first-class part of the Entity model. The extra link table is part of a standard hack to make a Relational Model represent more sophisticated object relationships.

S.Lott 2010-02-14 02:51:45

@S.Lott: They are called "Relational" databases, not "object oriented". Link tables are not business entities, but mapping to a Vector/ArrayList/Collection/etc is still an object equivalent.

OMG Ponies 2010-02-14 08:16:07

+4 A:

"entity and then a database table to persist it"

The Entity is what your program manipulates. That's the essence of what's being processed.

The database representation of that entity (like flat-file representations or GUI representations) are just handy representations of the entity.

You may have to think a bit about DB representation when it comes to certain things that relational databases are particularly bad at. Many-to-many relationships, for example, require introducing an extra table because the database has limitations that your object model doesn't have. You may have some entity design considerations to cope with this, but those a few and well-understood.

The database is less important.

The Entity definitions are central and essential.

S.Lott 2010-02-14 02:50:21

Interesting perspective. Opinions on this appear to be all over the map. The father of ORM, Dr. Raymond Chen, upon which LLBLGen is built, takes the direct opposite view point. The database drives the entities. See: http://wagnerblog.com/2009/10/llblgen-linq-nhibernate-an-embarrassment-of-riches/

Randy Minder 2010-02-14 02:59:30

@Randy Minder: It's simple logic. The application program actually does the actual work of the actual system. Everything else is presentation or persistence. The Chen references are very hard to track down. The previous wagnerblog entry references Peter Chen. Raymond Chen's blog doesn't mention ORM much. As a practical matter, the objects matter; the database is just persistence.

S.Lott 2010-02-14 03:44:39

I also agree with this answer. Another important aspect is that database schema contains just some aspects related to expected entity operation, i.e. you anyway need additional annotations describing such things as as lazy loading, association behavior and so on. The fact that database usually lives longer than the application does not mean you must design it first, if the tool allows to simply generate it. So I also look on database as on particular storage representation.

Alex Yakunin 2010-02-14 07:41:29

The database is capable of more than persistence. Business rules can be implemented, the data can be constrained... The benefit means offloading the overhead from the application, which can't compete with database processing. The centralization of business rules to the database makes developing in other languages easier - there's less to re-invent.

OMG Ponies 2010-02-14 08:22:30

@OMG Ponies: Some databases may be capable of having some application logic pushed into the database itself. Some argue that it's a good thing. It makes your application confusing (some parts are real code, some parts are stored procedures). It also makes things generally slower. Try a benchmark. Stored Procedures are not inherently fast; they're generally a wash. Do the design cleanly around model objects. Implement SP's only when you can **prove** that they improve performance.

S.Lott 2010-02-14 11:53:06

OMG Ponies 2010-02-14 18:51:56

@OMG Ponies: SP's are code -- never said otherwise -- please read carefully. SP's are confusing because the code is spread around. Ajax is the same problem. Code is spread around. DB cannot sort more quickly. Please measure this.

S.Lott 2010-02-15 01:21:05

Your comments states: **some parts are real code, some parts are stored procedures**. You clearly discriminate between database functions and stored procedures vs "real code". Code is also "spread around" in packages, APIs, etc. Lack of familiarity and/or comfort does not mean the database has shortcomings. For a profession that prides itself on learning new languages and such, I'm surprised by the apparent fear of SQL.

OMG Ponies 2010-02-15 02:54:53

@OMG Ponies: SP's are code. They're code in the wrong place. They're not like library API's because they're part of an application, not stores with the rest of the code. I don't fear SQL or SP's. I'm stating the facts as I experienced them. (1) they're not magically faster. (2) they're not magically more maintainable. (3) I've seen customers in deep trouble because of SP's. (4) I've helped customers rewrite applications because the SP's were so broken it could not be maintained. They should only be used when they can be **proven** to improve performance.

S.Lott 2010-02-15 11:07:01

@OMG Ponies: After 30 years in the business (8 of which I was an Oracle DBA) I think my opinion might be based on more than fear. It might be bad experiences tipping the scale toward object design as primary and database as persistence and stored procedures as (1) a tangible source of very real problems and (2) a *solution* to very few architectural problems.

S.Lott 2010-02-15 11:09:51

Time to play the experience card, rather than pertinent issues eh? With that much experience (including being a DBA), then I would expect you to be able to recognize that the data model is the foundation of what you can do in a database. If there are no primary keys, no indexes, no relational constraints - you might as well be using a flat file. All the situations you describe are symptomatic of poor data models - poor design overall. For all your years of experience, it is obvious those were not years spent working with enterprise scale applications.

OMG Ponies 2010-02-15 15:54:45

@OMG Ponies: The Object model is the foundation. It has several representations. primary keys, no indexes, no relational constraints are fine things to add to the relational implementation of the object model. No one said to omit them. Stored procedures are the only thing that cause problems. I'm stating the facts as I experienced them. (1) they're not magically faster. (2) they're not magically more maintainable. (3) I've seen customers in deep trouble because of SP's. (4) I've helped customers rewrite applications because the SP's were so broken it could not be maintained.

S.Lott 2010-02-15 16:36:04

*Business rules* are the foundation - objects spawn from them. Objects exist logically in the database as entities, physically they are tables. I've never said that SPs are a silver bullet, but that what you describe is symptomatic of fundamentally poor design. Only have to look at database related questions on SO to know that people can write bad queries - that's a lack of experience and/or understanding, not poor database performance.

OMG Ponies 2010-02-15 16:59:35

@OMG Ponies: The business entities are represented by an object model which includes processing ("business rules") and stateful attributes ("data that can be persisted"). You can implement it by scattering it into database, stored procedures and application code outside the database. Or you can create an object model which encapsulates everything and is persisted through the ORM. My experience is that the object model should come before the ORM design.

S.Lott 2010-02-15 21:00:03

+4 A:

Your database will likely outlive whatever application you build today. All the performance and the scalability are going to be driven by your database schema. A sound database model is the foundation on which any application is built, and I'd say is where you should invest most effort in design and testing, for it will give the biggest benefits.

That being said, of course your application will prefer to manipulate domain entities, and manipulating unnatural entities driven by relational theory as opposed to business entities will just complicate things. My view is that is the role of ORM to match the two, as best as possible. But whenever inevitable conflicts appear, the right of way should be given by the driving factor of your performance and scalability: the database schema.

Remus Rusanu 2010-02-14 06:00:12

@Remus - I think this is a very good perspective, and one I hadn't really thought about. The database, and the data in it, will likely outlive the initial applications that persist data to it. And it will potentially be used in many ways other than just the application that persists its data there.

Randy Minder 2010-02-14 13:31:17

ansaurus

tags:

views:

answers:

ORM - Does the Database Schema Drive the Entity Composition or Vice-Versa?

related questions