A discussion made mention of using an entity-relationship diagram (ERD) to understand and model a relationship graph. ERD tools were suggested. This led me to comment that the talk of ERD was somewhat off-topic. That said, I see the merit of ERDs in FileMaker development, so I thought I would start a discussion on the topic.
I believe FileMaker solutions should be backed by ERDs. A well formed ERD is implementation agnostic, optimizes data, documents the purpose of relationships, makes it easier to validate the data architecture of a solution and eases maintenance of a solution over time.
I know a fair amount of people who skip ERDs entirely and create only relationship graphs. Creating an ERD is an extra step that seems like a double-entry. If well done, there is also a data dictionary that needs to be created and maintained. If well done, selective denormalization must be documented. Customers do not see the value of an ERD and their associated documents. ERDs and their associated documentation need to be maintained outside the solution. Etc.
I agree that ERDs are likely overkill for simple solutions. I, however, often work on solutions that have planned evolutions and long lifespans. I also regularly work with IT people who integrate with my solutions. ERDs are, in my opinion, very much warranted in these cases.
However… ERD and relationship graph are not the same. For example:
ERDs allow for circular relationships. Not so for relationship graphs;
ERDs never have multiple occurrences of entities. Quite the opposite with relationship graphs;
ERDs should be normalized. ERDs normally need selectively denormalization to obtain desirable relationship graphs.
Do you produce or maintain ERDs for FileMaker solutions? Do you have any advice you want to share with us? Are there any tools you recommend?
Speaking of tools… @OliverBarrett suggested one in the discussion mentioned above that has the ability to create an ERD from a relationship graph and vice versa (if I understood correctly). I am curious to know how tools such as this one handle the above cases?
crows feet notation… if you don't know it, read up on it;
normalize to at least third normal form;
document relationships both ways – it makes understanding so much easier for people unfamiliar with ERDs;
make a data dictionary – it helps with selective denormalization, among other things;
document the rationale for each selective denormalization – future versions of FileMaker will make some of these obsolete, so it helps to know which denormalization could eventually be removed.
My tools
I use and recommend OmniGraffle from The Omni Group. It is an excellent diagram, prototype and design tool with built-in ERD primitives. The learning curve to use this application should be fairly short. This is a Mac / iPad / iPhone application. The Omni Group specializes itself in software for Apple ecosystem, so OmniGraffle is unlikely to ever come to Windows or Android.
Much as I like OmniGraffle, it does nothing to transform the ERD to any kind of programming code. I don't mourn this lack of capability because my ERDs are generally normalized and implementation agnostic. I do not see how a tool could handle selective denormalization? Then again, I hope to be shown otherwise in this discussion.
Unfortunately, I bemoan the fact that there is no standard ERD file format. Well… I know of none.
I also use and recommend BBEdit from Bare Bones Software. It still doesn't suck… or so the website says… and I agree with them. This is a text editor for programmers. Same as OmniGraffle… Mac only and not about to come to Windows. On the other hand, there are plenty of alternatives for all platforms.
In short, I stick to plain text files when it comes to documentation. Reason? Text format is compatible for years. Consider that text files from the earliest computers are still readable today.
For FileMaker, I recommend SQL Editor as it not only lets you build your ERD, it will create the database for you in FileMaker. It will also read the existing database from FileMaker. Much more than simple a drawing tool (though I'm an OG fan, also, when it's appropriate).
SQL Editor also works with any other RDMBS so it's a heck of an investment at only $79.
You mentioned that tool in the other discussion. I am curious to know how this tool handles table occurrences of the same table? Or circular ERD definitions when converting to FileMaker table occurrences?
The problem with MySQL Admin from my perspective is that it won't read/write FileMaker databases.
By "read" I mean you point the tool at your FMP database and it creates the ERD from it (reverse engineering). By "write" I mean after you make changes (presumably) to your ERD, the tool will go and update your FileMaker database (forward engineering).
For enterprise stuff, I use Navicat which has a really powerful modeler, but it won't write to ad-hoc JDBC connections.
SQL Editor is the only ERD tool I've found that will read/write FileMaker (and any other JDBC database) making it a real data modeling tool FMP devs could use.
Omnigraffle is excellent. I love this tool, but not for real DB modeling. Maybe some quick DB pictures. Just my opinion.