In anticipation of the hierarchical support to be released in EntitySpaces 1.5, we wanted to explain the relational object naming algorithm that we are using, what options will be available to you in the 1.5 release, and what we are planning for the future. We also would like your feedback on whether or not this implementation meets all your needs.

Your first question is probably, "What the heck is a relational object name, and why does it need an algorithm?" Take this code which will be possible using Northwind for example:

Employees emp = new Employees();
emp.LoadByPrimaryKey(1);

TerritoriesCollection terrColl = emp.Territories;

The first two lines are pretty routine. That last line does an awful lot for one line of code. That line traverses from an individual entity (Employees) through a many-to-many associative table (EmployeeTerritories - completely transparent) and retrieves a collection (TerritoriesCollection) of just the Territories related to that Employee. In EntitySpaces 1.4 there is no Territories object in the Employees entity class. The templates for EntitySpaces 1.5 will automatically generate a new object in the Employees class called Territories. "Territories" is the relational object name.

Your second question may be, "That seems pretty straight-forward. What's the big deal?" If you're a DBA or veteran coder that is familiar with complex databases, then you are already anticipating the problems that will arise if you need to generate unique names for all the relationships in a database. On the other end of the spectrum, you may be a programmer that understands parent and child relationships. You do not want to get a doctorate in hierarchical modeling. You just want relationships to work in your application. Out-of-the box, EntitySpaces 1.5, using the default settings, will generate hierarchical code that will work for 99.9% of real-world apps. It will be easy to use. And, it will offer some customization options to cover that additional 0.1% of the cases. This post is designed to give some insight into what the relational object names will be, how they will be generated, and why we chose that algorithm.

A Schema for Hierarchical Testing

Consider the tables and relationships in the schema diagram below. Most of you will recognize the similarity with Northwind. But, from a foreign key point of view, there are some significant differences. The schema is from the test database we are using for our hierarchical unit tests. It is specifically designed to stress test a full range of relationships and combinations of relationships. Here are some notable differences:

1. Customer has a composite primary key, but it is not part of a many-to-many. We have better design choices today, but this situation is not uncommon in legacy databases.
2. CustomerGroup links to Customer, but only uses part of Customer's composite primary key.
3. Order links to Customer through a composite foreign key; CustID and CustSub.
4. CustomerGroup and Group have a one-to-one relationship. This is unusual for a well normalized schema, but it is sometimes necessary. We have seen it in cases where a table ran up against a limitation in the database for the number of columns in a table. The columns had to be split into two tables with a one-to-one relationship.
5. The self-referencing foreign key has been retained in the Employee table, but was renamed Supervisor instead of ReportsTo.
6. There are two many-to-one foreign keys pointing from Customer to Employee; Manager and StaffAssigned. This is very common and tests possible naming ambiguity. If Customer had a relational object named Employee, you would not know which relationship it referred to.
7. In the link between Order and Employee, the foreign column name is the same as the primary column name. This is quite common, and tests a possible point of naming collision.
8. From a practical stand-point, there are two types of many-to-many relationships.
   • Simple cross-reference: This is illustrated by Employee - EmployeeTerritory - Territory. EmployeeTerritory has no user entered fields. The fields are all in the primary key. In this case, Employee will have a relational object named Territory and Territory will have a relational object called Employee. The EmployeeTerritory table is transparent in the EntitySpaces' hierarchical model. There are relational objects for EmployeeTerritory, but they are mainly there in case your schema changed (you added a non-key field to EmployeeTerritory) and it became a complex cross-reference.
   • Complex cross-reference: Order - OrderItem - Product is technically a many-to-many, but it has additional fields aside from the primary keys (e.g.; Quantity). EntitySpaces treats it as if it were two separate sets of relationships; Order - OrderItem and Product - OrderItem. The relational objects for both of these will be your primary working objects. We will show later how easy it is to access all the Product fields for each OrderItem of an Order. There is a relational object in Order named Product. But, again, it is primarily there to prevent break breaking changes during a regeneration of a changed schema.

Generated Relational Object Names

The table below contains the default names generated for each table above, the relationship it covers, and the return type. "Many To One" and "One To One" always return entities. "Zero To Many" and "Many To Many" always return collections. For the technically minded, there are dozens of combinations of cardinality, optionality, and direction which do not seem to be represented in the Relationship column. In reality, behind the scenes, EntitySpaces sub-groups everything into one of the four relevant categories it needs. And, to effectively use EntitySpaces you do not even need to be worried about those. They are pertinent to this discussion, but as a programmer, we will show later, how knowing the object you are in and the object you want to relate to, are the only two pieces of information you need to discover the power of hierarchical objects.

Usage Example:

We have a collection of Orders (Lines 1 and 2 in the code below). We grab one (3). We need to change the CustomerName for the related Customer (5). We need to set the OrderDate (6). (Notice that we use the EntitySpaces' StringProperty (str) to convert the string to a date for us.) We want to set the UnitPrice for all the OrderItems related to this Order (7 - 13). The price we want to use is the base UnitPrice set in the Product file for the Product related to the OrderItem plus 10% (9, 10). We want to save all changes (14). This blog is about names, but that last line certainly deserves some special attention. "ord" is part of a collection ("ordColl") so we need to call save on the collection, not the entity. That one line saves the changed Customer, the changed Order, and all the changed OrderItems, and it is wrapped neatly in a transaction. Your changes commit or fail as a unit.

1    OrderCollection ordColl = new OrderCollection();
2    ordColl.LoadAll();
3    Order ord = ordColl.FindByPrimaryKey(11);
4   
5    ord.CustomerAsCustID.CustomerName = "Test";
6    ord.str.OrderDate = "1999-12-31";
7    foreach (OrderItem item in ord.OrderItemAsOrderID)
8    {
9        item.UnitPrice = item.ProductAsProductID.UnitPrice.Value
10          * (decimal)(1.1);
11       Console.WriteLine(item.ProductAsProductID.ProductName
12          + " - " + item.UnitPrice.Value.ToString());
13   }
14   ordColl.Save();

A detailed examination of what is going on is the subject of another blog post. What we are interested in is more practical. As we are entering code, how do we know the relational object names that apply? I do not want to memorize hundreds of relations in dozens of tables. I do not care if it is one-to-many or a many-to-one, I need to get the Customer related to an Order. And, I need to know whether I am dealing with an entity or a collection. How does EntitySpaces 1.5 make my life easier? The answer resides in Intellisense. If the objects are intelligently named, they will be easy to find. Once found, Intellisense shows you what it is, and it will be easy to use. That brings us to our next subject...

Why Those Names?

Our goals for the generated relational object names were much the same as the goals stated in most naming guidelines. Sometimes seeking one goal will be in conflict with another, and compromises have to be taken. The one rule that cannot be compromised is that the names must be unique within the namespace and class. If the code will not compile, there is not much sense in continuing. Beyond that, here is what we aimed to achieve:

• Readability - The names cannot be so big and cumbersome that the code becomes un-readable.
• Understandability - You should be able to see the name and understand its function in relation to the class that contains it.
• Usability - Without intimate knowledge of the database structure, you should be able to find what you want in Intellisense and use it properly.
• Portability - This usually refers to portability between languages, case-insensitive languages in particular. But, we already give you the option of using the VB templates. Because we are generating names, by portability we mean the algorithm, too, has to be portable to a wide variety of database schema and still create unique names.

Here are the default algorithms, starting with the simplest:

• ForeignTableName - This is the simplest and clearest. In the very first piece of sample code at the start of the blog, you loaded an Employee. You know you need the territories for it, so next, in your IDE, you type "emp.ter". Intellisense pops up and you see "Territory". That looks good. Intellisense also shows that it is a TerritoryCollection. Now you know that you can foreach through it or bind it to a DataGridView. This algorithm only works for one-to-one and many-to-many. But, you do not need to worry about that.
• ForeignTableName + "As" + ForeignColumnName - At line 5 of the code sample above, you want to change the related Customer's CustomerName. You type "ord.cus". Intellisense has popped up. You see "CustomerAsCustID". That must be it, but why not just "Customer"? That is because the algorithm used for one-to-many and many-to one has to account for multiple links between the same tables. Say, down the road, your database schema changed and an Order had a BillToCustomer and a ShipToCustomer. The column name is used as a tie breaker. This is seen in the ForeignKeyTest diagram with the two columns in Customer that are related to Employee. Intellisense shows you that both EmployeeAsStaffAssigned and EmployeeAsManager have return types of "Customer". This is another clue and also lets you know that they are entities. They can be treated like any other entity. The appended column names "StaffAssigned" and "Manager" let you choose the right object for your task.
  Let's look at the foreach in line 7. This falls under the same algorithm. You want to change each of the OrderItems related to the Order. You type "ord.ord" and see OrderItemsAsOrderID and that it returns an OrderItemCollection. Now you can loop through it like any other collection, but it will only touch those OrderItems that are related to "ord".
• ForeignTableName + "As" + ForeignColumnName + "Parent"
• ForeignTableName + "As" + ForeignColumnName + "Children" - These two work in tandem. They are necessary because of self-referencing foreign keys. The Supervisor column in Employee is both a one-to-many and a many-to-one on the same table. An additional tie breaker is needed. You type "emp.emp". Intellisense shows both EmployeeAsSupervisorChildren and EmployeeAsSupervisorParent. You need to make a decision. Fortunately, you have another piece of information in Intellisense. EmployeeAsSupervisorChildren is an EmployeeCollection, while EmployeeAsSupervisorParent is an Employee (a single entity). EmployeeAsSupervisorChildren is the collection of employees that this employee supervises. EmployeeAsSupervisorParent is the Employee (single entity) that supervises this employee. Your choice should be clear based on your intended use.

Notice that the ForeignTableName is always there and always first. That is the key to finding it in Intellisense. You end up with either a collection or entity of just the related rows. You use them like any other EntitySpaces BusinessObject. They are lazy-loaded. (Retrieving all Orders does not automatically load all related OrderItems, Customers, etc.) Plus, you get the benefits of transactional saves. In the schema above, you can traverse all the way down from an Employee through Customer, Order, OrderItem, and Product. Or, you can head up the other way from Product. This is all most developers will need most of the time. But, what about those rare instances when a generated name collides with a Property that already exists in the class? That brings us to the next subject...

Customizing the Generated Names

In most cases, the defaults will suffice. But, what if the compiler says you have duplicate names? Or, what if your organization has Domain Specific Language policies? When you run the EntitySpaces' hierarchical template, you are presented with a few new fields that give you some flexibility on the generated names.

The above image is from the EntitySpaces template UI for 1.5 which is currently in testing. It may change depending on your feedback from this post, but it shows the current defaults. "Table" and "Column" are derived from esPlugin "Entity" and "PropertyName", respectively. By default, this just trims illegal characters and PascalCases the foreign table and column names. The easiest way to get rid of compiler errors is to add a prefix. "Referenced" for One and "Referring" for Many will let you eliminate both suffixes. The disadvantage is that typing "emp.ord" is not going to find the Order object on an Employee entity. You will need to know to type "emp.ref". On the other hand, all referential objects will be lumped together in Intellisense. You may find it easier to deal with them that way.

A really easy way to get all hierarchical objects in one area is to add a simple prefix like "fk" or "hier". FxCop will hate this, since Hungarian notation is definitely out. But, if you feel it abides by the four naming goals listed earlier, and makes you more productive, then we say go for it. On a side note, if your table and column names conform to Microsoft's guidelines, the default names will pass FxCop's naming rules. But, remember, if you have a column named "CustomerID', then FxCop will complain and suggest "CustomerId". If the column is "CustId", then FxCop will grouse about "Cust" not being in its dictionary and suggests not using abbreviations. You could go through your database and change the offending column and table names. Or, you could decide that FxCop is just quibbling and it is safe to ignore those apparent violations. 

By default, the suffix is only appended to self-referencing foreign keys. This keeps the other names shorter. If you feel that a suffix would give the extra information you need to distinguish collections from entities, then removing the check will put the suffix on all generated names. It could, also, be used as another way to eliminate duplicate names for the compiler.

This is a good point to talk about our future plans for hierarchical naming. Imagine a template UI that displayed all the relationships and the default object names. It let you Alias any or all the names with something of your own choosing and remembered your choices. That is the goal for a future release. If you have played with the ASP.NET Admin Grid template, or checked out the PDF instructions for it, you know how convenient being able to see the columns and the related fields can be. The UI code needs to be ported over from that template, and we would ideally prefer an approach that did not require setting up one table at a time. But, once 1.5 is out, and we have coded, tested, and released the requested features promised for 1.6, we intend to devote some time to this.

We want your feedback.

Between us, we have thousands of man-hours of practical experience with database coding going back over 20 years. But, we still learn something new almost daily. We are hoping our customers and other forum members will share their experience and help shape EntitySpaces. We know that the relational object names we are using are a bit on the ugly side. But, that is the compromise we chose to reach the goals set out earlier. If anyone has suggestions for improving these, we will gladly consider them. You can post your comments here or in the forums. We eagerly await your response...

- The EntitySpaces Team