Monday, June 25, 2007

how not to structure a database

This is what I've been working on for the past three weeks: a circular relationship map for the database I'm supporting, reverse engineered using Embarcadero ER/Studio. There are no indexes other than primary keys defined and there are no foreign keys in any tables so the relationships have to be inferred from the stored procedures. This explains why a 5GB database with 120 users is running on Windows Server 2003 x64, dual dual-core CPU's and 8GB RAM.

Oh, and those tiny little blips in the upper left corner are views with no columns defined. I haven't figure out why they're in the database yet.


  1. I think, if you stare carefully at the structure you presented, you can actually see what appears to be a pentagram in the middle. :-)

  2. Hey Charles! Greg Keller here from Embarcadero. My Google crawler picked up your thread. ER/Studio was my 'baby' dating back 10 years or so. I’ve since passed it on to a great PM team in San Francisco and Monterey where it is developed. First of all THANKS for using ER/Studio! As it relates to what you are seeing vis-a-vis the layout, in all sincerity, this is a highly compressed algorithm to assist with the optimization of memory consumption for large models. In other layout examples (e.g. hierarchical), the CPU utilization is way more intense as it has to parse through, calculate then lay out all of those nodes and edges to give you a more meaningful lay out. So, circular, while cool in a psychedelic sort of way is about as useful to a data architect to see patterns in a large model as [insert bad analogy here]. The idea here is to expand the tree of the Main Model and to start to identify sub groupings. For example, pick out an entity of table you know is a core part of your concentration via the Explorer tree. Right mouse and create a Submodel. While in the Submodel editor, allow the product to go and scan for n-levels of children upstream and downstream from that selected entity. Before you know it, you are working in meaningful Submodels to help focus your attention to on (and are INFINITLEY more easy to lay out.

    Hope this helps and ALWAYS feel free to contact our support team in SF who are awesome!

    Greg Keller
    Vice President-Product Management
    Embarcadero Technologies, Inc.

  3. How old is this database (what version of MSSQL was it orginally built under?)

    If I remember correctly, there was a 'bug/feature' in one of the older versions of SQL Server (maybe 6.0?) where a temporary view could be created 10x faster if the database already had a view defined with the same name(regardless of what it contained.) Then rather than destroying the view completely, you just rebuild it with no columnar attributes...or something like has been quite a while since I did any serious SQL Server programming.

    Those views without columns maybe a remnant of that bug/feature, I am pretty sure it has been 'fixed.'

  4. Tony - I'm sure you could eventually derive any number of suggestive, alarming or simply bewildering images. I'm running dual 19" wide screen monitors with this stretched across both of them and it's still not big enough.

    Greg - Wow, I'm surprised this showed up on your radar, much less so quickly. I know the circular model isn't intended to really help a data architect. After letting the hierarchical run for 6 days I called support and was told to do this so I could at least start running reports, then let it do the layout to hierarchical. Thanks for the info on letting it create submodels from upstream/downstream elements. That should be tremendously helpful.

    Andy - I don't know when this was implemented in SQL Server, but it's currently on SQL Server 2005 Enterprise.