FileMaker And The ERD

From Dwayne Wright PMP - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

I’ve talked about what database related entities are and how you can build FileMaker tables to work with them. In a working relationship, we see that many tables work together to perform a set of tasks for the individual, the business or the organization. You can see that in a fairly complex database, you may want to have a road map of all these intersections.

The Entity Relationship Diagram (ERD), is a documentation tool for database projects and can be similar to a rough blueprint for building a house. The primary goals of a successful ERD is to ...

- identify primary database entities
- identify the attributes of each entity (to some degree)
- identify the relationships between entities using links between attributes

There isn’t a single ERD type and there are only a few hardened rules to follow. Many believe that it is best to adhere as closely as possible to industry standards and fully document where you may stray from the established formats.

Some have commented about the similarities between an ERD and the FileMaker Relationship Graph. There are some stark differences between the two but one can  help influence the other. Ideally, if you are going to create an ERD it should be in place before Relationship Graph takes shape.  

The thing about the Relationship Graph that greatly strays from the ERD mentality is that each entity can (often must) appear multiple times within the relationship graph. This is absolutely not the case with an ERD and makes it a bit challenging to blend the two for the non full time FileMaker developer.

FILEMAKER AND THE ERD IN THE REAL WORLD
I have to admit that I have rarely been asked to provide an ERD to a customer or a project manager. In fact, the only time I’ve been asked to create one is when I’m being considered for an open position as a FileMaker developer for a company. I’m chuckling to myself as I write this, because that is a valid reason to be familiar with the concepts of an ERD.

Recently, I was asked “in one page, draw an entity relationship diagram for a simple product sales solution. You may limit the detail to primary and foreign keys”. So I had to go back and familiarize myself with the particulars of ERD creation again. Here is what I came up with and I’ll explain what I was trying to do.

CLIENTS: Related to zero or more invoice records.

** It is possible a new client has gotten credit approval but hasn’t placed an order yet.

INVOICES: Optionally related to one client.
Mandatory Related to at least one (probably more) invoice line items

** This is a bit of a stretch because I have seen invoices that have no line items and a shipping charge. However most invoices print from the line items table. So if this isn’t a mandatory relationship, it might not be printable. Also, it may be a business rule that all invoices be linked to a client record.

INVOICE LINE ITEMS:
Mandatory relationship to one invoice
Optional relationship to one inventory product

** This is a bit of a stretch but I’ve seen instances where customers want to enter in something in a line item that is not an inventory product.

INVENTORY: Related to zero or more invoice line item records.
Related to zero or more purchase line item records.
Related to zero or more inventory adjustment records.

** It is possible an inventory product hasn’t been sold or ordered yet.

ADJUSTMENTS: Mandatory related to one or more inventory records.

VENDORS: Related to zero or more purchase order records.

** It is possible a new vendor has not been used on a purchase order yet.

PURCHASE ORDERS: Optionally related to one vendor.
Mandatory related to at least one (probably more) purchase lines

** Although I think it is best practice that a purchase order be linked to a vendor record, I have seen customers requesting otherwise.

PURCHASE LINE ITEMS: Mandatory relationship to one purchase record
Optional relationship to one inventory product

** This is a bit of a stretch but I’ve seen instances where customers want to enter in something in a line item that is not an inventory product.

© 2010 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.