I have 3 entities - mileage, reimbursement and timecard. With a recent change in requirements I need to store up to 10 additional user inputs per entity. Each entity has the same 10 additional inputs. Call it input1 through input10. Each one is a simple text field.

So a user can enter input 1 - input10 when adding/updating mileage, reimbursement and timecard data. The contents of the fields are different depending on which entity we are dealing with, but the schema is identical for these 10 fields.

Maybe its a mental thing but looking at these 10 additional inputs they dont strike me as the building blocks of a supertype. If anything they seem like additional appendages, not foundational elements shared by the 3 entities.

Is there a way, with inheritance to design a shared table thats not a supertype? Maybe I can add a field that identifies the entity a given row belongs to (entitiytype) plus a field that contains the PK of the entity it points to . So that one row in this table can be joined to mileage, another joined to reimbursements and third to timecards.

Something also feels odd about my proposed solution. I'm not sure its a good idea or that it can be accomplished via inheritance. Whats the correct way to deal with a situation like this?

Thank you !

Hi there,

In my opinion, you don't need Inheritance here. Just because some tables looks alike or share part of their structure doesn't mean that you have to go through inheritance. If they don't relate each other with a subtype->supertype association, then don't use inheritance.

Normalization says that you have to group those common fields in another set. A good example is the Address table. Instead of create the same ten address fields for every table that need address information, some architects creates an Address table with an AddressId PK, and the dependent tables point to it, either in a 1:1, m:1 or m:n association. Anyway, you should evaluate if this is really your case. Maybe in the feature they will ask you to specialize those ten fields, or maybe this abstraction is just a way to save coding time, but in the end will affect your structure. So, just think if this is really your case.

So, I think that you have some options here:

A) Value Types. Create individual fields for each table. No Inheritance and no separate table, just the raw tables. Even so, you can refactor the common fields into a Value Type that is convenient in your generated code.

B) Additional Interfaces. This is same to oprion (A), except that you don't use Value Types, but just make your common entities to implement some interface you already coded. This is useful to group behaviors, anyway what interfaces just do.

C) Create a separate table, then point all related tables to it, but don't create inheritance, just a normal relationship.

So, you should evaluate your scenario and see what is more convenient. Hope that helps

Thank you David. The most inviting solution you listed is the one where "you create a separate table and point all the others to it ". If I went that route, it means I need to do two things : For add / update calls, I would need to traverse the relationship and add the given row of values into the shared table. For read calls I would probably want a typed list to flatten out the connection between the entities. In order to represent a timecard with its additional fields. Is that what you meant with option C)

Thanks Thomas

Hi Thomas,

For the option (C) if you want to create, assuming it's a 1:1 relationship, you would do something like:

var customer = new CustomerEntity();
customer.Name = "ACME Ltda"
customer.Contact = "Wile E. Coyote";
customer.Phone = "5555";

var address = new AddressEntity();
address.Country = "USA";
address.City = "Los Angeles";
address.Line1 = "...";

// assign the new address to the new customer
customer.AdminAddress = address;

// save it recursively (whole graph)
using (var adapter = new DataAccessAdapter())
{
     adapter.SaveEntity(customer, true, true);
}

For fetching you would use normal entity + prefetchPath. Something like:

// instantiate the entity with the ID we want to fetch
var customer = new CustomerEntity(1522);

// define the prefetchPath graph to use in the fetch
var path = new PrefetchPath2(EntityType.CustomerEntity);
path.Add(CustomerEntity.PrefetchPathAddress);

// fetch the graph
using (var adapter = new DataAccessAdapter())
{
     adapter.FetchEntity(customer, path);
}

// access the fetched graph;

txtName = customer.Name;
txtAddressCountry = customer.Address.Country;
...

Thank you David. That's the route we went. One small mod on our end... we used a typed list for "read" part in several places.

Regards, Thomas