Introduction
In my previous article Romeo and Juliet - Making Relationships First Class Citizens, I wrote an application for
developing an entity relationship meta-model of the characters and events in Shakespeare's play Romeo and Juliet. In this article, I'm going to use the
exact same application to create a schema for how to model this model. The schema is of course general purpose, it can be used for any meta-model of
information. Being a schema, it can be backed by a physical database and/or a .NET DataSet, both of which you will see here. I also create a
"super-user" UI for entering the meta-model data, navigating relationships, and of course I continue using GraphViz to illustrate the schema as well as the
meta-model instances. Hopefully by the end of this article, you will have an appreciation for my particular form of insanity, which is abstracting
concepts such as tables, fields, and foreign keys into the general concepts of entities, attributes, and relationships, and you will have a sense of how a
dynamic environment such as this can be used for modeling, data mining, and relationship building!
Architecture
I'm going to make a controversial decision with regards to the implementation - we only need two primary collections: EntityInstance and RelationshipInstance.
Yes, there are supporting classes like Attribute and Audit, but what I want to point out is the architectural decision to put all entity types (Person, Name,
Action, Place, etc.) into a single collection. The other option would be to create separate collections. There is definitely an argument in favor of the latter approach when it comes to code
generation, performance, indexing, and so forth. However, I want to defer those considerations for the simplicity of two collections, one for entity
instance, one for relationship instances. The drawback is that type information has to be explicitly stated in each record rather than implicitly
determined by the collection type (list, table, etc). Ideally, the implementation should be decoupled from the presentation (model vs. view model).
I might in a future article look at using typed collections as an alternative implementation.
An EntityInstance record requires:
- A unique ID
- A reference to the entity type
A RelationshipInstance record requires:
- A unique ID
- A reference to the relationship type
- The "Entity A" ID
- The "Entity B" ID
An EntityAttributeInstance record requires:
- A unique ID
- A reference to the attribute type
- A reference to the Entity instance
- The attribute value
An AttributeAudit record requires:
- A unique ID
- Reference to the changing AttributeInstance record
- Who created/changed
- When created/changed
- Previous value (if changed)
Using the same tool to visualize the entanglements in Romeo and Juliet, we can also visualize the model above:
Deleting Entities
Normally, entities and relationships are never deleted. A relationship is expired, and this can affect the "state" of the relationship (died,
acquitted, destroyed, etc.) Obviously, when editing data, it is useful to be able to perform a hard delete: a typo was entered, a completely erroneous
record exists, the conscious destruction of information, and so forth.
- The user is only allowed to delete an entity if the entity is:
- not referenced in a relationship
- has no audit trail other than "create".
- If the entity is referenced in a relationship, the user must confirm that the relationships will also be deleted. The user might also be
given an option to delete the related entities being referenced in those relationships.
- If the entity has audit records other than "create", the user must confirm that the entity and its audit trail will be deleted.
- Deleting entities might require "supervisor" privileges or similar.
Deleting Relationships
A relationship instance only exists if the entities in that relationship exist. Deleting a relationship requires a confirmation, as we're breaking a relationship between two entities.
Changing Relationship Entity References
Once created, changing the entities that a relationship references requires a confirmation. This is not tracked in the audit.
Orphan Entities
These are entities that are not in relationship. It is simple to query all entities not referenced by relationships to obtain this list.
Creating the Database
I don't like doing things manually when I can write some automation to do what I want. So, along those lines, it's straightforward, given the above
entity relationship model, to create the database for the necessary tables. Also, because I want to keep this work as portable as possible, I'm using
the SQLite database in these articles. It's easy enough to add support for your favorite database provider.
Two things that become obvious that is missing in the properties of the instances is the schema information necessary to define the foreign key
relationships, as well as the data types. It's easy enough to add the necessary properties in the Attribute class and the Relationship class,
and we also need to be able to name the entity types.
Given this (now a more complete model), the SQL to create the database can be generated. Also, a complete model, supporting the entire set of properties
in the relationship schema, is:
A full size version is here. (Ooh, and look, the relationship names are now in braces, and I figured out how to force a new
line in the Graphviz label: you have to specify "\\n"!).
Note the inclusion of the List and Pair collections and the DescribedWith property of a Relationship - not sure if I'm actually going
to use that property, but it's there for now, with the idea that a relationship might have attributes itself, which are defined by associating a relationship type with an entity.
Creating the Tables
SQLite does not support adding constraints, such as foreign keys, in the "alter table" command. This is annoying because it means that tables have
to be created in a particular order so that the FK can be resolved. Furthermore, reading the docs, it looks like foreign key constraints are not
enforced unless you explicitly enable them with a "pragma" command. So, we'll deal with this later, relying instead on .NET's DataSet to enforce the
constraints when the user edits the data.
Creating the tables without constraints is straightforward. I implemented an IDataProvider interface, so we can create tables in different
database implementations, or, as you'll see below, in an in-memory DataSet. The salient code for working with SQLite is:
public StringBuilder GetCreateTableSql(Entity entity)
{
string comma = string.Empty;
StringBuilder sb = new StringBuilder();
sb.Append("create table ");
sb.Append(entity.Name.Brace());
sb.Append("(");
entity.EntityAttributes.ForEach(a =>
{
sb.Append(comma);
Attribute attr=Schema.Instance.GetAttribute(a.Name);
sb.Append(a.FieldNameOrName.Brace());
sb.Append(" ");
sb.Append(dataTypeMap[attr.DataType]);
if (a.IsPrimaryKey)
{
sb.Append(" PRIMARY KEY AUTOINCREMENT NOT NULL");
}
comma = ", ";
});
sb.Append(")");
return sb;
}
Creating the DataSet
For an initial pass of populating the data, I want to create a DataSet and a "superuser" UI, at least something to get us started with populating
data. A separate implementation of my IDataProvider interface creates a DataSet:
namespace DataSetDataProvider
{
public class DataSetProvider : IDataProvider
{
protected DataSet dataSet;
protected Dictionary<DataType, System.Type>
dataTypeMap = new Dictionary<DataType, System.Type>()
{
{DataType.Bool, typeof(bool)},
{DataType.DateTime, typeof(System.DateTime)},
{DataType.Int, typeof(int)},
{DataType.String, typeof(string)},
};
public DataSet DataSet { get { return dataSet; } }
public DataSetProvider()
{
}
public void CreateDatabase(string name, bool replaceExisting)
{
dataSet = new DataSet(name);
}
public void CreateTables()
{
Schema.Instance.EntitiesContainer.ForEach(t => t.Entities.ForEach(e =>
{
DataTable dt = new DataTable(e.Name);
DataColumn primaryKey = null;
e.EntityAttributes.ForEach(attr =>
{
Attribute ropAttr = Schema.Instance.GetAttribute(attr.Name);
DataColumn dc = new DataColumn(attr.FieldNameOrName,
dataTypeMap[ropAttr.DataType]);
if (attr.IsPrimaryKey)
{
primaryKey = dc;
}
dt.Columns.Add(dc);
});
dt.PrimaryKey = new DataColumn[] { primaryKey };
dataSet.Tables.Add(dt);
}));
}
public void CreateConstraints()
{
Schema.Instance.RelationshipsContainer.ForEach(r=>r.Relationships.ForEach(rel=>
{
Validation.Validate(!string.IsNullOrEmpty(rel.ForeignKeyAttribute),
"A relationship requires designating the foreign key field for " + rel.EntityA);
EntityAttribute attr = Schema.Instance.GetEntityAttribute(
rel.EntityA, rel.ForeignKeyAttribute);
string fkFieldName = attr.FieldNameOrName;
DataColumn fkColumn = dataSet.Tables[rel.EntityA].Columns[fkFieldName];
Validation.Validate(dataSet.Tables[rel.EntityB].PrimaryKey.Length > 0,
"No primary key has been set for the entity " + rel.EntityB);
DataColumn pkColumn=dataSet.Tables[rel.EntityB].PrimaryKey[0];
DataRelation dr = new DataRelation(rel.Name, pkColumn, fkColumn);
dataSet.Relations.Add(dr);
}));
}
}
}
Developing the User Interface
To begin with, we'll create a simple user interface that lets us pick any entity as a starting point for editing and navigation of parent and child relationships:
There're three things that need to be added to this basic UI:
- Lookup (comboboxes) for displaying something a bit more user friendly for the foreign key IDs
- Navigate to child or parent options
- Navigation "bar", showing how we are moving around the tree
The first item, displaying user friendly foreign key data, has a particular nuance to it, illustrated by the RelationshipInstance entity, namely that this
table references a table (EntityInstance) which itself has no user displayable fields - it's just comprised of IDs. To resolve this issue, I created a
"Comment" field that can be used to provide information about the model to the developer, even though the concrete data values are themselves stored in the
database. This is an artifact of the level of abstraction that I've put into the model, and the "Comment" field is a workaround to support that abstract.
Navigation
We can populate the master and detail comboboxes easily enough, allowing us to navigate to master and detail tables.
Getting the Master Entities List
public List<string> GetMasterEntities(string entityName)
{
List<string> ret = new List<string>();
RelationshipsContainer.ForEach(r => r.Relationships.ForEach(rel =>
{
if (rel.EntityA == entityName)
{
if (!String.IsNullOrEmpty(rel.ForeignKeyAttribute))
{
string masterEntity = rel.EntityB;
if (!ret.Contains(masterEntity))
{
ret.Add(masterEntity);
}
}
}
}));
return ret;
}
In the above code, we treat EntityA as the "detail", and therefore if a relationship exists to
EntityB in which a foreign key field (in EntityA) is specified, then we have a reference to a master table. You will note that I'm
not creating a list of Entity instances, rather, I'm creating a list of entity names. I have little objection to this - it isn't necessary to have objects flying about all the time.
Getting the Detail Entities List
public List<string> GetDetailEntities(string entityName)
{
List<string> ret = new List<string>();
RelationshipsContainer.ForEach(r => r.Relationships.ForEach(rel =>
{
if (rel.EntityB == entityName)
{
if (!String.IsNullOrEmpty(rel.ForeignKeyAttribute))
{
string detailEntity = rel.EntityA;
if (!ret.Contains(detailEntity))
{
ret.Add(detailEntity);
}
}
}
}));
return ret;
}
Here we have the opposite: if relationship defines a foreign key field for EntityA when EntityB (considered the master) matches
the desired name, EntityA is the detail. Perhaps better names for EntityA and EntityB would actually be "Detail" and "Master"!
Tracing Our Navigation
Next, I want to be able to trace my path through the navigation and provide the ability to unwind the trace. Visually, we'll display the user's navigation like this:
This is all UI behavior, we still have to implement the meat and potatoes. But first, getting the grid to display comboboxes, which is the gravy we need
for the meat and potatoes.
Foreign Key Lookups
First, we need to create some data, so let's start with the EntityType data, using our Romeo and Juliet schema:
After also populating the AttributeType table, we can now populate the EntityAttributeTypes table (refer to the schema in the Romeo and Juliet article):
This is handled by the following code. Note the use of the DataView to sort the master table's display field values, and note that the display field is
itself determined from a schema property in the Entity definition.
protected void InitializeColumns()
{
dgvModel.Columns.Clear();
DataTable dt = dataSet.Tables[dgvModel.DataMember];
foreach (DataColumn dc in dt.Columns)
{
bool isLookup = false;
foreach (DataRelation dr in dt.ParentRelations)
{
if (dr.ChildColumns.Contains(dc))
{
DataView dvParent = new DataView(dr.ParentTable);
string displayField =
Schema.Instance.GetEntity(dr.ParentTable.TableName).DisplayField;
dvParent.Sort = displayField;
DataGridViewComboBoxColumn col = new DataGridViewComboBoxColumn();
col.DataSource = dvParent;
col.ValueMember = dr.ParentTable.PrimaryKey[0].ColumnName;
col.DisplayMember = displayField;
col.HeaderText = dc.ColumnName;
col.DataPropertyName = dc.ColumnName;
col.ReadOnly = false;
col.Width = 120;
dgvModel.Columns.Add(col);
isLookup = true;
break;
}
}
if (!isLookup)
{
DataGridViewTextBoxColumn col =
new DataGridViewTextBoxColumn();
col.HeaderText = dc.ColumnName;
col.DataPropertyName = dc.ColumnName;
dgvModel.Columns.Add(col);
}
}
}
Navigating to Selected Detail Records
So, now we're ready to add a few more bells and whistles so we can navigate to selected detail records. For example, from Entity Type we want to
navigate to Entity Type Attributes, showing all attributes of the entity type "Name":
To:
Notice the navigation textbox now shows the qualifier "[EntityTypeID=2]". The relevant code for this is:
private void btnSelectedDetails_Click(object sender, EventArgs e)
{
DataGridViewSelectedRowCollection selectedRows = dgvModel.SelectedRows;
string orRow = String.Empty;
string currentEntity = dgvModel.DataMember;
string navToEntity = cbDetailTables.SelectedItem.ToString();
StringBuilder qualifier = new StringBuilder();
if (selectedRows.Count > 0)
{
foreach(DataGridViewRow gridRow in selectedRows)
{
string orRelationship = String.Empty;
foreach (DataRelation dr in dataSet.Relations)
{
if ((dr.ChildTable.TableName == navToEntity) &&
(dr.ParentTable.TableName == currentEntity))
{
qualifier.Append(orRow);
qualifier.Append(orRelationship);
qualifier.Append(dr.ChildColumns[0].ColumnName);
qualifier.Append("=");
string pkField = dr.ParentTable.PrimaryKey[0].ColumnName;
DataRow row = ((DataRowView)gridRow.DataBoundItem).Row;
qualifier.Append(row[pkField].ToString());
orRelationship = " or ";
orRow = String.Empty;
}
}
orRow = " or ";
}
UpdateGrid(navToEntity);
SetRowFilter(navToEntity, qualifier.ToString());
ShowNavigateToDetail(navToEntity, qualifier.ToString());
}
}
The above code constructs a qualifier where the foreign key of the detail table is = to the primary key value of the master table. Note that the above code handles two important cases:
- The user selects multiple rows in the master table.
- The detail table has multiple fields in relationship with the master table. An example of this is the RelationshipInstance table, which has
three separate fields in relationship to the EntityInstance table: EntityAID, EntityBID, and DescribedWithEntityID.
An example where both of these cases come into effect is when navigating from several selections of EntityInstance to the RelationshipInstance table, an example qualifier
looking like: "<-- RelationshipInstance [EntityAID=2 or EntityBID=2 or DescribedWithEntityID=2 or EntityAID=1 or EntityBID=1 or DescribedWithEntityID=1]".
I had a dickens of a time figuring out how to set the correct default DataView. I tried these two options (which I came across on various websites):
Neither of which worked, finally stumbling across this solution:
protected void SetRowFilter(string entity, string qualifier)
{
DataView dv = (DataView)((CurrencyManager)BindingContext[dataSet, entity]).List;
dv.RowFilter = qualifier.ToString();
}
Navigating to Selected Master Records
This is essentially the reverse process, in which the qualifier is constructed as the primary key of the master table is = to the foreign key value of the detail table.
private void btnSelectedMasters_Click(object sender, EventArgs e)
{
string currentEntity = dgvModel.DataMember;
string navToEntity = cbMasterTables.SelectedItem.ToString();
DataGridViewSelectedRowCollection selectedRows = dgvModel.SelectedRows;
StringBuilder qualifier = new StringBuilder();
string orRow = String.Empty;
if (selectedRows.Count > 0)
{
foreach (DataGridViewRow gridRow in selectedRows)
{
string orRelationship = String.Empty;
foreach (DataRelation dr in dataSet.Relations)
{
if ( (dr.ParentTable.TableName == navToEntity) &&
(dr.ChildTable.TableName==currentEntity))
{
qualifier.Append(orRow);
qualifier.Append(orRelationship);
string pkField = dr.ParentTable.PrimaryKey[0].ColumnName;
qualifier.Append(pkField);
qualifier.Append("=");
DataRow row = ((DataRowView)gridRow.DataBoundItem).Row;
string fkField = dr.ChildColumns[0].ColumnName;
qualifier.Append(row[fkField].ToString());
orRelationship = " or ";
orRow = String.Empty;
}
}
orRow = " or ";
}
UpdateGrid(navToEntity);
SetRowFilter(navToEntity, qualifier.ToString());
ShowNavigateToMaster(navToEntity, qualifier.ToString());
}
}
Navigating Back
Navigating back through the navigation stack simply requires parsing the navigation record and setting the row filter:
private void btnBack_Click(object sender, EventArgs e)
{
string qualifier;
string navItem = PopNavigation(out qualifier);
UpdateGrid(navItem);
SetRowFilter(navItem, qualifier);
}
Now for Something Really Snazzy
With a little bit of code that knows about our model:
protected void OnGraphRelationshipInstances(object sender, EventArgs args)
{
if (dsp == null)
{
CreateDataSet();
}
DataSet dataSet = ((DataSetProvider)dsp).DataSet;
if (dataSet.Tables["RelationshipInstance"].Rows.Count == 0)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "DataSet Files (.dataset)|*.dataset";
ofd.RestoreDirectory = true;
DialogResult ret = ofd.ShowDialog();
if (ret == DialogResult.OK)
{
dataSet.Clear();
dataSet.ReadXml(ofd.FileName, XmlReadMode.IgnoreSchema);
}
}
StringBuilder sb = new StringBuilder();
sb.AppendLine("digraph G {");
List<string> entityInstanceList = new List<string>();
foreach (DataRow row in dataSet.Tables["RelationshipInstance"].Rows)
{
int entityAID = Convert.ToInt32(row["EntityAID"]);
int entityBID = Convert.ToInt32(row["EntityBID"]);
DataRow entityAInstanceRow = dataSet.Tables["EntityInstance"].Rows.Find(entityAID);
DataRow entityBInstanceRow = dataSet.Tables["EntityInstance"].Rows.Find(entityBID);
string entityADescr = entityAInstanceRow["Comment"].ToString();
string entityBDescr = entityBInstanceRow["Comment"].ToString();
int entityATypeID = Convert.ToInt32(entityAInstanceRow["EntityTypeID"]);
int entityBTypeID = Convert.ToInt32(entityBInstanceRow["EntityTypeID"]);
DataRow entityATypeRow = dataSet.Tables["EntityType"].Rows.Find(entityATypeID);
DataRow entityBTypeRow = dataSet.Tables["EntityType"].Rows.Find(entityBTypeID);
if ((entityATypeRow["Name"].ToString() != "Name") &&
(entityBTypeRow["Name"].ToString() != "Name"))
{
string label = String.Empty;
if (!entityInstanceList.Contains(entityADescr))
{
entityInstanceList.Add(entityADescr);
sb.Append(entityADescr.Quote() + "\r\n");
}
if (!entityInstanceList.Contains(entityBDescr))
{
entityInstanceList.Add(entityBDescr);
sb.Append(entityBDescr.Quote() + "\r\n");
}
if (row["PairItemID"] != DBNull.Value)
{
int pairItemID = Convert.ToInt32(row["PairItemID"]);
label = dataSet.Tables["PairItem"].Rows.Find(pairItemID)["Comment"].ToString();
}
sb.Append(entityADescr.Quote() + " -> " + entityBDescr.Quote() +
" [label=" + label.Quote() + "]\r\n");
}
}
sb.AppendLine("}");
Generate(sb);
}
We can generate an instance relationship model. This lets us visualize the actual instances of the data relative to their relationships:
This is just a subset of the whole instance graph, which you can view here.
Another Snazzy Thing
We can also inspect the relationships of entities to each other by filtering for a specific entity instance. For example, by selecting the fight between the servants:
we get this lovely graph:
Or, by selecting Romeo:
Hopefully you are seeing the usefulness of this architecture!
What About...
The Database?
My initial thinking was to persist the model data in the database, hence the part about SQLite. As it turns out, it's trivial to persist the DataSet
itself, so I'm not even using the database at the moment! I'll leave the finishing touches for another time.
DevExpress?
Let's face it, the .NET DataGrid is ugly. I had also originally thought about leveraging the drill down capability of GridControl so that I wouldn't
have to deal with the details of the navigation. It turns out that this wasn't exactly what I was looking for in terms of the UI behavior. Furthermore, the RelationshipInstance table
resulted in a particularly odd issue. Because there are three relationships to EntityInstance, the drill down feature of the GridControl was creating
three tabs, one for each relationship. This really wasn't what I wanted--I wanted a single view of the RelationshipInstance records, regardless of whether the reference to the selected
EntityInstance occurred in the EntityAID, EntityBID, or DescribedWithEntityID fields. So, after rethinking the goals of the UI and
how to do the best implementation, I chose a single grid and reverted back to .NET's grid control, eliminating the need to force people who want to download the code
to also have DevExpress. A win-win situation on all fronts, in my opinion!
Custom UIs?
At this point, I have a "super-user" UI, which is not at all what I want to present the end-user with. The end-user UI should look and feel like a
direct implementation of the model, rather than what I have achieved at this point, which is a meta-model UI. But, this was a necessary step to get to
the end-user implementation, and is also a valuable tool for perusing the data at the meta-model level.
Audit Trails?
That'll be implemented when I do the custom UIs.
Things I'd Like to Improve?
There are a couple things that bother me.
- The first is the "Comment" field in entities that are manage only relationships -- they contain only foreign keys. Ideally, I'd like to
be able to specify that a Person entity's display value is determined by its relationship to the Name table and the value in the Name field. As it stands,
it's easy to forget to create a Name instance and the relationship between a Person, Place, or Action instance to a Name instance.
- It would be nice to be able to compose the display field. For example, in the PairItem table, I'd like to automatically compose the
display value from the EntityAValue and EntityBValue fields.
- Lookups - the PairItemID field, rendered as a combobox, should really be qualified by the Pair types allowed for the particular relationship type.
There are other cases like this as well.
And Why?
You might be asking yourself, why is this useful? After all, I'm destroying the classical approach to database development, abstracting out the
concepts of tables, fields, and foreign key relationships. The reason is this: by achieving this level of abstraction, I can dynamically create new
relationships to new data, allowing an application to develop without having to go back to the "hardcoded" aspects of an application: the entity model,
the database schema, the user screens, etc. For example (somewhat contrived), let's say you have a database of patients along with a list of drugs that have
been prescribed to the patient. At some point, you want to make it easy to tie in to a database of drugs as well so you can easily look up a drug's use,
interactions with other prescription drugs, and so forth. The theory is, that with a model like this, you add the necessary types, attributes, and
instances and create the relationship between "prescribed drug" and "prescription drug info" and voila, you have enhanced the application simply
by adding new data and new relationships. Now, let's say you want to also include interactions between herbal remedies and prescription drugs.
Same process. This achieves the goal that I set out to achieve - making relationships first class citizens, in other words, the relationship is treated like any other piece of data.
Running the Demo
To explore the features of the demo, fire it up and load the file "rop.model":
Note that you cannot use the Model functions (under the Model menu) to edit the "romeo_and_juliet.model" model. All of the functions under the "Model"
menu assume that you are working with a database schema definition in which primary key fields and foreign key fields in relationships are identified.
So, after loading "rop.model", which defines a schema, select "View Data"
from the "Model" menu. In this dialog, open the file romeo_and_juliet.dataset. You can then explore the data that I've
put into the model. The RelationshipInstance entity is a good place to start:
Changing the Model
If you edit the model in the first screenshot:
- Close (if it's open) the Model Data Viewer.
- From the "Model" menu, select "Create Data Set". This updates the
DataSet with the new model. - Select View Data and re-load the datatset.
References
