Please critique my ERD structure - Consignment Inventory, Customer Side

[beckeeper]

Hi everyone,

 

I'm pretty new to FileMaker but I've had quite a bit of experience designing databases for MySQL and web applications. So I've got a pretty good handle of how to design a database and define relationships, but this one has been throwing me for a loop.

 

I've been trying to figure out for ages how to streamline data entry for the company that my husband and I own, I was working on a web solution and then found FileMaker and realized that, if I can get it set up right, it will do exactly what we need.

 

Our business works like so (it's so unlike the traditional "Customer orders products 1, 2, and 3 and we invoice them for those" that I feel I have to explain it):

 

We run a product consignment program for customers. When we get a new customer, we provide them with a "menu" of products they get from us. For example, Customer A has products 1, 3, 5 and 7. Customer B might have products 2, 3, 6, 7, 8 and 9. But no customer is ever going to have every product that we carry.

 

When we "install" the products for the first time, we create a par level for each product. So Customer A's par level for Product 1 might be 10. We check each customer periodically throughout the month (or maybe not at all if they are slow) and, ideally, we add product to bring each par back to its preferred par level. At the end of each month, we count the products and the customer is billed for what they used.

 

Every time we go to check a customer's consignment inventory, we use what we call a Service Form (it's similar to a Purchase Order, I suppose). A Service Form contains the Customer's name, date serviced, who serviced it (our representative) their product menu and pars, as well as the product counts: Previous Count (which is pulled form the previous Service Form's Final Count), Current Count, Number Replaced, and Final Count. This then gets a signature and is emailed to certain people on the Customer side and our side. Currently, I create a Service Form for each Customer with their particular Products List as a fillable PDF and we use iPads and capture signatures, so my end goal is to be able to have FileMaker parse all the data from Service Forms utilized by FileMaker Go.

 

Example of a Service Form:

 

So, if a Customer is busy, we might see them every week and have a new Service Form with lots of entries for each product in the Number Replaced column.

The Service Forms' data will be parsed at the end of each month after the last service call has been done, and invoiced for what was used.

 

It just seems like a lot of complex relationships, and I'm not entirely sure I have my ERD set up just right to handle it all.

 

Here is what my ERD looks like right now:

 

Can anyone see any errors with this, or should it work? I'm anticipating the trickiest bit is going to be the scripting to populate the CustomerProducts with the correct ServiceFormLineItems.

 

Thanks!

Bec

 

[Fitch]

I'm anticipating the trickiest bit is going to be the scripting to populate the CustomerProducts with the correct ServiceFormLineItems.

 

I see CustomerProducts as the "standing order" for the customer, and ServiceFormLineItems as the record that the order was filled. So it's v./versa from what you stated, and I wouldn't expect it to be particularly tricky.

 

It might be useful to include begin/end dates in CustomerProducts. Then when you populate the line items, you'd just find all the products for that customer that haven't expired, and loop through or import into line items.

 

Finally, don't forget the FileMaker mantra: the Relationship Graph is not an ERD.

[beckeeper]

Thanks, Fitch. I hadn't heard the bit about ERDs vs. relationship graphs.

And you're right, I did have that totally backwards... CustomerProducts and ServiceFormLineItems. I was brain fried this morning for sure.

The CustomerProducts are actually pretty static; we only change menus once a year, so I'm not sure that begin/end dates are necessary. I guess what's really confusing me is how I'm going to set up the portal in the layout to filter everything correctly, but that's for another forum.

I'm glad you didn't see any glaring errors in my sample though, I guess I got all my relationships correct!