All of a sudden, something interesting regarding the physical database design of a hibernate-driven application came to my mind and I just want to get your ideas and views on that. As a persistence layer, Hibernate provides an abstraction over the database and allows to define foreign key relationships and cascade database operations easily. Generally, when we design databases we create foreign key relationships and cascade modes on database tables themselves and map them in hibernate also. But if want, it is still possible to have hibernate mappings of foreign key relationships between entities and keep the database tables (physical database design) unlinked through foreign key constraints. If we do so, we come up with a physical database design where tables do not have any relationships between each other on the database table level. But through hibernate, we have defined the foreign key relationships and cascade levels properly resulting the data integrity that we expect to have.
I just want to know if it is ok to do so. Any problems in that approach? Do we really need to maintain the entity relationships at database table level?
Any ideas on that matter? Your responses are greatly appreciated.
When you say physical model do you really mean a logical model? A physical model is the model you provide that does all the database specific stuff (e.g. using an Oracle specific data type, or defining a SQL Server collation etc.). A logical model describes the entities and their relationship with each other in a database agnostic way.
Getting the logical model right is really important. You need to be able to trust the data obeys the rules you expect - this is one of the reasons you use a relational database, rather than simply using some amorphous data store. If you try to enforce relationships between entities using a client applciation you have to ensure that is the only application that connects to this database which is fairly limiting. Get the logical model right any everything else comes form tha, if you get it wrong you will be working round it (and cursing yourself!) for years to come.
Yes, I did really mean logical database design. Sorry for the mistake. The point you have highlighted is really important. If the application is one and only one application that goes to use that db, it does not make any problems. But, still there can be future possibilities to others system use the same database. In such a scenario, big problem might occur with respect to the data consistency.
Thank you very much for your response. greatly appreciated.