Saturday, May 19, 2012

Data Modeling Rematch!

So I've got a lot of data modeling ahead of me at work and I need tool. The system was being tracked in Visio and that doesn't scale, for the reason I've noted before. So I did a breakdown before of the choices but I wanted to address my specific needs this time.

I like ModelRight, a lot. If you read the review you know it has an excellent working style. The mode-less property boxes are incredibly efficient and let you make changes remarkably quickly.

I also really enjoy ER/Studio Data Architect (referred to as ER/Studio thoughtout this post). The User Interface is extremely smooth and responsive. I do find that the modal dialog boxes slow me down a bit.

So I've been doing a bake-off between the two. They both support the same core functionality really well (forward and reverse engineering) and are fairly stable.

I'm working with a larger (~700 table) model and seeing how it does.

Data Modeling

So which is faster for actually developing a new model?

ER/Studio

This is very much an awkward process of dragging the table on to the screen, double-clicking it and adding entries to the table. There are custom domains that can be defined to group similar types together and provide guidelines to improve consistency. The tabs can be awkward and it feels like a very stilted process.

ModelRight

This is a bit more natural because after you drag the table over, you can start editing on the canvas or go to the docked modeless window. The same domain features are available to provide consistency for your columns and types. The modeless windows really do make this a much faster process to create and modify the tables.

Model Layout (Reverse Engineering)

ER/Studio

ER/Studio offers several different layout models to improve layout accuracy
  • Circular
  • Orthoganal
  • Hierarchical
These help to various extents based on your sub-model contents.

The canvas the ER/Studio has is unmatched for it's smoothness when manipulating the entities and adjusting the layout.

ModelRight

ModelRight only has auto-arrange, with an adjustable number to indicate spacing.
ModelRight does it better (after fiddling with the number).

The diagram needs less adjustment for clarity even if the UI isn't as smooth as ER/Studio offers.

Reporting

It's important to be able to distribute the model. Typical options for this are JPG, XML, HTML and RTF.

ER/Studio

  • JPG: Good
  • XML: Limited information is available but ok
  • HTML: Good. It's a nice tree structure and they provide a fairly good interface for it
  • RTF: Good. It's a word doc, nothing super fancy but it is pretty clear.

ModelRight

  • JPG: Good
  • XML: Good. Super Detailed info in this file
  • HTML: Brutal to setup. Basically it is an XML export that expects you to transform it with an XSLT. Complete Customization, but out of the box it is weak. On the other hand, the help file says that ModelRight support will help you craft any XSLT you need.
  • RTF: I don't think it's offered. May be available as an XSLT from support.

Sales and Support

ER/Studio

I've been going through the Embarcadero sales rep whenever I needed anything. There was some initial lag on responsiveness but when it was clear I was serious, things progressed well. She brought engineering on to a call to demonstrate functions they felt were superior to their competitors. It was very interesting and involved a lot of add on products.

I also contact support for two incidents. There was about a one day lag between requests for help and responses. Neither incident was resolved but the staff was courteous and tried to help.

ModelRight

The sales rep has been very responsive through email, but that was the only communication channel. I questioned her about features in the next two versions and the answers were useful without over promising.

Support has been remarkable. The support team was a bit terse in it's replies to my questions but always responded quickly. They went so far as to make an application build with fixes for bugs a couple of issues I found the same day. This support is amazing considering I'm still running the trial version.

Defaults

I'm not even sure if the section should be in here but I think it is important from the standpoint of balance. 

ER/Studio

The defaults here are so good that I didn't even realize that a can configure a lot of the options to do something different. A lot of thought went into the initial configuration of out of the box. 

ModelRight

This feels like it was developed by programmers. For example

  • The screen defaults to being overly crowded (even at 1600 x 900) with too many widgets active by default. 
  • Reports REQUIRE an XSLT to be legible outside of Internet Explorer.


On the other hand, this leads to things like

  • A complete XML export of the model is available and can be diff'ed with older versions
  • Quick short-cut to take a JPG of your diagram
  • Ridiculously powerful scripting


Conclusion

ER/Studio is an excellent, polished tool. I have the budget to purchase it for myself and my team but not all of the secondary features. I could not get the repository or portal functionality which is what I need to be able to distribute the model to the development team.

ModelRight has similar (or better) features when compared to the baseline ER/Studio package. The price is about half but it also includes a free viewer and more flexibility if you are interested in putting a bit more work into it. Future enhancements slated for ModelRight 4 look very impressive and I'm glad to be on board with them for this project. 

TLDR: ModelRight wins! Embarcadero has a lot to worry about with ModelRight. I will be using them as my data modeling platform of choice for the foreseeable future. 

Wednesday, May 2, 2012

Sure it scales UP, but does it scale DOWN?

I've recently run into an interesting issue, scalability doesn't necessarily go both ways.

Now, intellectually I understand that you make trade-offs to scale to millions of users but that doesn't help my "gut feeling" that I am doing something wrong when I accept the trade-off.

Let's take the classic example of triggers. Triggers tend to be very wrong when it comes to performance, however they are highly effective at securing the data and ensuring business rules are followed. I weigh the need for triggers very carefully before I add them to any table. This can be much more effective than adding the logic to every system that must integrate with the database.

Small databases allow you to pull all sorts of fun tricks that you would never consider otherwise.

Performance concepts do not always scale down to provide the flexibility that is needed on some projects. I've been measuring system with 500GB as a baseline for a while now and forgetting about the ~100GB options.

Right now, I'm looking at different ways to do row, column and cell level security. Almost none of these are viable in a large database.  In fact, most of these are not even viable in a "small" database but this gives me a lot of freedom to experiment with concepts, which is fun.