O/R mapping

[Serge Lobko Lobanovsky]

Hi people, I would like to gather your opinion on the following subject. I am working on a generic O/R mapping tool. Currently, it supports the following database-to-class situations: 1) 1 class per database table 2) 1 class per view which maps into one database table, or maps into several database tables, however, the fields which are required for update, still stay in one table. I'm thinking of another approach: to have a few database tables for all types of objects in a business domain, something like this:

ClassesTable
Class Type
Entities.Order
Entities.Customer

ClassFieldsTable
FieldID - Class Type - FieldName
1 - Entities.Order - orderID
2 - Entities.Order - orderItem
3 - Entities.Customer - custID
4 - Entities.Customer - custName

FieldValuesTable
ID - FieldID - Value
1 - 1 - ord1
2 - 2 - Sock
3 - 3 - ALADDIN1
4 - 4 - Aladdin
5 - 1 - ord2
6 - 2 - handkerchief
etc.

The tables are related in the following way: ClassTypes 1 ------ (ClassType) ------- * ClassFieldsTable FieldValuesTable 1 ------(FieldID) ----- 1 ClassFieldTable The O/RM layer dynamically constructs SQL commands to store and load business objects to/from the database, and to perform different types of queries on objects in the database with the help of object-oriented query builder. The reasons behind this are: 1) Predefined database structure that will not change with the development of the business application 2) Possible centralized storage for enterprise-level data to support long-running business transactions and a common lock mechanizm 3) Still possible to create reports with 3d party tools by using views into the "basic" tables Can you point me to any disadvantages in this approach? Regards, Serge (Logic Software, Easy Projects .NET site)

[Steven Campbell]

1. Performance. For queries, because you always need multiple joins, and for inserts and updates, because you have a bottleneck in the FieldValues table. 2) Data types - you have not mentioned how you will solve the data type problem...you need either 1 table for each data type, or each data type needs to be represented in the FieldValues table, or something inbetween. Its been done before - I know of one commercial product that uses this technique. I think the project manager did his thesis on it, and concluded that it was a viable way to go. I'm not so sure.:|

---

my blog

[Serge Lobko Lobanovsky]

Hi, Steven! Thanks for your reply. Steven Campbell wrote: 1) Performance. For queries, because you always need multiple joins, and for inserts and updates, because you have a bottleneck in the FieldValues table. Can you eloborate a bit on this? 2) Data types - you have not mentioned how you will solve the data type problem...you need either 1 table for each data type, or each data type needs to be represented in the FieldValues table, or something inbetween. One thing that comes to my mind is to use SQL Server variant data type. Regards, Serge (Logic Software, Easy Projects .NET site)