Thursday, December 23, 2010

Supplemental BIM Database Porn Part 1, Introduction and Table Schema

Now that I've got your attention...

Have you ever wanted to know a little more about databases in the context of your everyday BIM related tasks but weren't quite sure where you should turn? Then this post is for you and if you're by yourself, nobody will be there to laugh at you! This is part 1 of what will more than likely be an entire action packed mini super series on how databases can help the BIM process and lead to your ability to conquer the world.

It is fairly common to manage design specifications for repetitive yet varying design elements for us in the AEC industry. Some example categories that are typically handled in such a way are Furniture, Furniture Systems, Door Hardware, Light Fixtures, and Casework to name a few. These items are commonly only tagged with a type value in the model and then synced with an external database where they can be further elaborated on.

So what's a good schema to use for a database design to solve such a task when your BIM application is say... Revit? Well, Revit stores data for it's elements in two basic forms that are basically entirely separate. Type data is stored in a "Family Symbol" where all of an element's "Type Parameters" live. The rest of the data describing an element in the model is stored as "Instance Parameters" within each instance of a placed symbol. So to mirror this in the database world, it makes sense to have one table to store an element's type properties and another related table to store the instance properties. It also makes good sense to have a matching pair of these tables for each Revit category that your elements belong to that you are interested in synchronizing.


A consistent naming strategy for these category based tables will also help you succeed. I like to use a simple prefix of "inst" for the instance properties and a prefix of "type" for the type property tables. I then just name each table with a suffix matching the Revit category name.

You may have also noticed in the image up above that the same "inst" and "type" prefixes are used for the primary keys of each table. This also helps with the relationships between the database and Revit. I prefer to use the "UniqueID" property for all elements as their primary key when synchronizing with a database. These values are pretty much guaranteed to never duplicate even across project models. The value returned by UniqueID is a hugungous GUID. I then use the UniqueID from the type element as a foreign key relationship to the instance tables. This is possible since the type element (family symbol) and the instances of these symbols as they are placed throughout the model are all unique! That is to say that the type element is entirely a separate object behind the scenes in the Revit API thus making a handy way to join them in a database schema.


I also generally use the name of the Revit parameter or property to name the corresponding database field name. String based parameters and properties should have a string formatted database field. Double formatted Revit parameters or properties should have their database fields formatted as decimal. Be careful with Revit parameters that are named using what are referred to as "Reserved Names" as these may result in strange unwanted features during synchronization. A list of these so called reserved words can be found at http://support.microsoft.com/kb/286335.

Stay tuned for future episodes of this action packed and exciting topic!!! We'll also get into how these tables and databases can be setup programmatic!!! Enormously exciting and guaranteed to help you pick up chicks at a bar!!