Using an ORM with a database that has no defined relationships?
- by Ahmad
Consider a database(MSSQL 2005) that consists of 100+ tables which have primary keys defined to a certain degree. There are 'relationships' between tables, however these are not enforced with foreign key constraints.
Consider the following simplified example of typical types of tables I am dealing with. The are clear relations between the User and City and Province tables. However, they key issues is the inconsistent data types in the tables and naming conventions.
User:
UserRowId [int] PK
Name [varchar(50)]
CityId [smallint]
ProvinceRowId [bigint]
City:
CityRowId [bigint] PK
CityDescription [varchar(100)]
Province:
ProvinceId [int] PK
ProvinceDesc [varchar(50)]
I am considering a rewrite of the application (in ASP.net MVC) that uses this data source as is similar in design to MVC storefront. However I am going through a proof of concept phase and this is one of the stumbling blocks I have come across.
What are my options in terms of ORM choice that can be easily used and why?
Should I even be considering an ORM? (The reason I ask this is that most explanations and tutorials all work with relatively cleanly designed existing databases, or newly created ones when compared to mine. I am thus having a very hard time trying to find a way forward with this problem)
There is a huge amount of existing SQL queries, would a datamappper(eg IBatis.net) be more suitable since we could easily modify them to work and reuse the investment already made?
I have found this question on SO which indicates to me that an ORM can be used - however I get the impression that this a question of mapping?
Note: at the moment, the object model is not clearly defined as it was non-existent. The existing system pretty much did almost everything in SQL or consisted of overly complicated, and numerous queries to complete fucntionality. I am pretty much a noob and have zero experience around ORMs and MVC - so this an awesome learning curve I am on.
