Today I was creating an Entity Relational Diagram (database diagram) for a database that was generated by Entity Framework, using convention over configuration to link the database tables to the object entities.
As I have said in the past, a well normalized database may often represent a good class hierarchy. For example, a customer entity may have a collection of employees, purchase orders, etc. In a database, these collections are translated into the relational equivalent of a customer table, an employees table, a purchase orders table, etc. They also have relationships of one to many from the customer to the employees, one to many from customer to purchase orders, etc.
This is where I think convention starts to break down. In order for Entity Framework to interact with the database and objects, it utilizes a naming convention. The convention is that all tables must have an unique primary key column of numeric type, and it has to be named id. This is a Microsoft technique they push in all their documentation, and it is one that I don’t necessarily like.
I lean more to the school of using composite primary keys. In my opinion, a many to many join table should not have a single system assigned unique value as the primary key. It should be a composite key comprised of the primary key values from both parent tables involved in the many to many join. This is not to say you couldn’t have an identity sequential column for the purposes of data storage optimization in SQL Server. But it does not have to be the primary key for this table; and is in fact added deadweight.
In this case, convention over configuration forces you to generate a poor schema because the tool is not as smart as you. In my opinion, any tool using configuration in order to optimize code writing must also have the ability to use a different technique when convention does not follow practices of your application.
What do you think? Am I just being ornery in my old age, or is this really an area where we need improvement? Send your thoughts to btaylor@sswug.org or leave your comment here online.
Cheers,
Ben