There are a few ways to accomodate reflecting inheritance in DBs. I'll use the example that Organizations and Individuals are both types of Contacts.
1) Contact Table (containing common fields), Organization Table (containing organization specific fields), Individual Table (containing individual specific fields). FKs from Organizations and Individuals to Contact.
2) Contact Table (containing all common fields, all organization fields, and all individual fields). No Organization or Individual table.
3) Organization Table (containing common fields and organization fields) and Individual Table (containing common fields and individual fields). No contact table.
There are +s & -s to each, though for my purpose #1 is the right way to go in this situation.
My problem right now has to do with the concept that everything in my system in the end shares a common base class - "Entity". Entity contains at this point created and modified datetime/user fields along with a System Guid key. Much of me is leaning towards solution #3 for this, storing the created and modified datetime and user on each table pertaining to a Csla class (and eliminating the foreign key to the Entity table which will no longer be there).
I would be doing this because I think the managibility is better without the entity table and all of the foreign keys and all the instances where the stored procedures would need to accomodate the entity table. The Entity base class in the system will be consistently exposing these fields but I'm not convinced I want a separate Entity table in the database.
Has someone else broached this question? What avenue did you take and were you happy with the direction you took?
Yes, I am in the middle of designing this type of system myself. I have done a prototype system to my satisfaction and I am moving into the final coding on my BOs. My post at the bottom of this thread explains generally how I laid out my tables and how they map to my BOs.
http://forums.lhotka.net/forums/thread/6189.aspx
Thanks for your reply - I guess this subject may be a little different than what is on that thread.
A lot of us have a base class derived from BusinessBase<T> that we derive all of our base classes from, and mine happens to have a unique system Id of Guid along with some common properties for all objects.
Seeing that virtually all of my classes will inherit through this scheme and have need to persist these properties, I am just struggling on whether the best route is to keep these properties in a parent "Entity" table in the database or in individual places throughout the database to negate the need for bucketloads of foreign keys to this "entity" table and the slightly more complex stored procedures, etc.
Ah ok.....sorry I misunderstood. I am not using Guids
If I understand your issue correctly, you are looking at finding a way to follow an object-oriented, normalized approach to the database design. And, the pattern you are describing is not uncommon. We have a similar situation where users can occupy different "roles" (not security roles) and, as a result, have different information stored.
For instance, we have an extra-net application where employees, contractors, customers and vendors can all log-in and gain access to their profile information as well as site feature designed for them. The latter is handled by the security model via roles; but, obviously, the profile information for each will be different. E.g., an employee won't have a billing account.
However, there is much in common between them such as username and password, name, e-mail address, mailing address, etc. So, the question is, and I believe yours is as well, do you create a common Contacts table containing these fields and have your Employees, Contractors, Customers and Vendors table relate to this "parent" table, duplicate the columns in each of the individual tables or have a single table for all with some potentially null columns depending on the type of user.
I have heard many arguments for and against each approach and long ago stopped listening to the DBA's because I believe that even though you marked this as an off-topic post, it really isn't because this decision has to be based on your BO design. What works from the code's point-of-view because you can make all three work at the DB level.
In an object-oriented world, it makes perfect sense to create a Contact base class (or Actor as in our case) and derive Employee, Contractor, Customer and Vendor objects. It's creating the hook into the database that presents the issue. How best to do this?
Unfortunately, I don't have a quick answer for you because we've run into the same problem. And while we've implemented an approach, I don't like it. It's not intuitive and is quite cumbersome. So, with this clarification in mind, I'd like to throw open the discussion to others for input.
What is basically boils down to is how to create a hierarchy of business objects within the CSLA framework? When you have a base class that has its own unique identifier derived by another class with its own unique identifier, how do you handle that?
I've posted a new topic to discuss this point in more detail (http://forums.lhotka.net/forums/thread/6449.aspx).
Hope this helps.
Thanks for your post.
I must not be asking my question very clearly because my question isn't so much about the business related inheritance - I think it makes a lot of sense to have, for instance, a base Contact class and subclasses of Organization and Individual. And I think it makes a lot of sense to have a contact table in the database and two "subclass tables" for Individual and Organization.
The one I can't quite justify necessarily is the inserted class between Contact and BusinessBase<T> - that is, Entity<T>, which will be where I put my special logic for BusinessBase<T> as well as maintain some information about changed and modified information for that business object. (Both children and roots have modified and changed information).
In my database, this isn't a question of adding the contact table and having two foreign keys to contact from Organization and Individual. This is a question of adding an Entity table and having literally 10, 20, 30...+ tables foreign key to Entity for their modified and changed information.
i.e., do I have:
Contact: Id, Status, ..., ModifiedUser&DateTime, CreatedUser&DateTime
ContactName: Id, Name, SortName, ..., ModifiedUser & DateTime, CreatedUser&DateTime
EmailAddress: Id, Description, EmailAddress, ModifiedUser & DateTime, CreatedUser&DateTime
Or do I have one Entity table with:
Id, ModifiedUser&DateTime, CreatedUser&DateTime
Along with the tables that foreign key to entity:
Contact: Id, Status
ContactName: Id, Name, SortName
EmailAddress: Id, Description, EmailAddress
---
It's clear what seems "clean" is the separate entity table, but I am not convinced that's what I want to do - all BO fetches need to join an extra table, have to be more attentive in my stored procedures to the foreign key to Entity (& make sure I don't orphan Entity records when deleting BOs), etc.
---
Ah, got you.
In our case, we have a design & development standard in place that works with our framework in this regard. Each and every table is to have columns for the UniqueID, CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy and Deleted values. To support this, all of our business objects are derived from our base class which implements these properties. So, using your terminology, we do have an Entity<T> class and is does reside between BusinessBase and our BO's - this allows for deviations from the standards if necessary (the BO can derive directly from BusinessBase in that case).
We do not have a separate table to track these fields as that would get too complicated (identifying which record goes to which table, etc.).
This is where the question of inheriting business objects comes into play. When you have an object that is created by joining two of these tables - which UniqueID, CreatedOn, etc. applies? It also applies to complex many-to-many relationships where the relationship is an entity of its own with properties such as AddressType or whatever.
Hope that's more inline with what you were looking for.
Yes, this is more along the lines of what I was talking about.
"We do not have a separate table to track these fields as that would get too complicated (identifying which record goes to which table, etc.)."
I think what you're saying is that your "Entity" table wouldn't know exactly what type it referred to. I definitely understand you here, but in this instance I'm not sure if the Entity table would need to know which type it referred to. It's kind of this "consistent bucket" for the changed & modified information.
"When you have an object that is created by joining two of these tables - which UniqueID, CreatedOn, etc. applies?"
Not every table would link to Entity. For instance, Profile & Individual are two tables that combined comprise the base attributes and properties of an Individual, and Profile & Organization are two tables that combined comprise the base attributes and properties of an Organization.
So in the case of classes that result with subclassed tables and so forth, only the "parent" table would contain the modified and changed information.
With a case where, for instance, a profile has multiple alternative names, each alternative name would have a modified/changed information too (subclass of Entity).
----
I would really like to understand more about why you don't have a separate table - which aspects get too complicated in your opinion?
Thanks for taking the time.
Copyright (c) Marimer LLC