FileMaker And The ERD

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

I had a customer requiring me to document software requirement specifications for each phase of our project. I suggested doing an ERD as well and they liked this idea. So I thought I'd share what I came up with in a blog post.

WHAT IS AN ERD?
The Entity Relationship Diagram (ERD), is a project management 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.

OUR ERD
You will see an entity called Payment Adjustments. The deal is that insurance companies may often miss paying a claim by a few dollars. It isn’t worth their time to try to get this money from the customer or the insurance company, so they are writing it off. You may think this should be an attribute of applied payments entity and not an entity in itself. In this case, the sheer number of records, the security settings around who can give a payment adjustment and the reporting needs led me to the decision to propose it as a separate entity.

You may need to click to expand

Here are my thoughts ...

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.

PAYMENTS: Optional one to many relationship to applied payments

** This is a bit of a stretch but it is possible that deposits or future gift cards might exist that have not been applied to a payment yet

APPLIED PAYMENTS: (join table) Mandatory relationship to a single invoice and a single payment record

ADJUSTED PAYMENTS: Mandatory relationship to a single invoice

** This could be a type of applied payment without an associated payment but I recommend it be in a separate table for reporting and security needs

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.

INVENTORY 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.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - 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.