2 Replies Latest reply on Feb 20, 2016 9:20 AM by lcot17

    ERD Assistance/Guidance - CRM Database


      Hiya - I am on this forum quite a lot reading up, researching, learning and asking for support so firstly i'd like to thank the community for the assistance and help thus far!


      I am working towards A) learning FMP14 in more extensive detail and B) creating a CRM/Sales Management database that moulds to the requirements of my business.


      My business is a clothing company who design/manufacture/brand and sell clothing B2B and B2C.


      I am planning out the database tables and relationships with an ERD and this is what i've got so far....


      Screen Shot 2016-02-20 at 00.31.49.png


      The database will comprise of a number of sections:

      1. Customers
      2. Products
        1. Barcode Pool (a pool of unique EAN and UPC barcodes)
      3. Invoices (for B2B sales)
      4. Online Orders (for B2C sales - a weekly overview of online sales in order to keep watch on inventory)
      5. Inventory Orders
      6. Reports (will get to this at a later stage)
      7. Dashboard (again, will get to this at a later stage)



      The customers list layout will consist of a list of all customers in alphabetical order. Click on a customer, the edit customer layout will consist of key customer information, a portal listing all customer orders with their current status, CRM communication log (links open a popover).

      The CRM log will consist of a date, communication type, contact spoken to and notes. These will be listed by date (newest first).



      The products list layout will consist of a list of all 'ACTIVE' status products. At the top of this layout will be radio/checkbox filters to filter by category/season etc.

      The product edit layout will consist of general product information, a product options section where I can add a product size option as defined in the 'Option Set' table. Each product option has its own unique SKU!! Therefore in the product options I also need to define each SKU with a unique barcode selected from the barcode pool table. Once a barcode has been used and assigned a product ID / option ID, it cannot be selected again and is removed from the available list (This is an area I need to check relationships/tables as I am not sure if it will work the way I have it).

      Finally I will have an area to list all inventory orders for that product/product option and a graph to show total units sold for that product.


      Barcode Pool

      Simply a list that I can import from a *.csv file. When I acquire more barcodes for products I get a fixed number of barcodes (EAN-13 and UPC). I can upload these to the table creating a new record for each barcode.


      Perhaps in this pool I can have a product ID fk and an option ID fk to assign a barcode to a specific SKU. I can use a popup or dropdown list to show all of the barcodes with an empty product ID / Option ID field? I would need to validate the EAN number field also to ensure it is unique in the table and also that the same product ID / option ID combination cannot be entered twice? Not sure how to achieve this. Basically, trying to avoid using the same barcode twice and avoid accidentally assigning two different barcodes to a product.



      In this layout I can create an order to a customer in the customer table. This will work very similar to the starter solution invoice but I am hoping to add a invoice log section to log transactions paid in, commissions paid, communication made with customer etc. The money / commissions paid in / out will offset the balance for that specific order. This way I can track and update the status of an order from open to complete.


      One consideration I am going to likely encounter is that a product has up to 5 size options as defined in ProductOptions table. The invoice item list portal will need to have a QTY field for each possible option. Once an invoice is complete, the qty will deduct from the qty level of that specific product and option! How would this work? Does my ERD work for this?


      Online Orders

      Similar to invoices except it isn't assigned to a customer. I can create a daily / weekly invoice/order summarising the online sales received. This will deduct stock quantities also just like invoices.


      Inventory Orders

      When I make a PO with a supplier, I will log this in this section. Just like an invoice, except QTY is added rather than deducted.

      I can enter the supplier information (maybe another table to outline suppliers), PO information, products ordered, qty of each SKU and the price paid.



      I will deal with these later! But imagine the starter solution dashboard (i'll take inspiration from this) and for reports I need to be able to output charts/reports to print or view showing Total Sales in a variable period, total commissions, total spent on stock, monthly profits etc.



      Key Issues

      - Barcode relationships with products and product options.

      - Products working with Product Options - how does that translate to InvoiceLineItems and InventoryOrders?


      My invoice portal will follow this format:

      Item NameModel #SMLXLOSTotal QTYUnit PriceDiscountTaxableLine Price


      So, there is my plan!

      Is any kind person willing to read through this plan and critique it / improve it to ensure it 1) works and 2) is efficient with what I am trying to achieve!!




        • 1. Re: ERD Assistance/Guidance - CRM Database

          Just some thoughts, hopefully helpful and not meant to be criticisms:


          On the CRM side, I'd recommend separate tables for People and Companies, or as we do it, a Contacts table where each record is one or the other, but not both. Also a join table to show relationships between the two, or at least a 1:M from Companies to People. The reason for this is that contacts exist outside of their role as customers. Your employees, the people that have user accounts, prospects, and the guy you order pizza from at lunchtime are all contacts, but they may or may not be Customers. If you create contacts now, and link your customer to a contact record, that contacts table will become useful in many other places down the road. If you just have customers you can only track a subset of the contacts you deal with, and any other subset will require new tables, potentially with duplicate contact information.


          Also, I don't see tables for contact information (phones, faxes, emails, websites, addresses), but any one of these things could be many records for the same customer/contact. Having only fields for this, or worse, using "phone1, phone2", will likely end up being a limitation that you and your users will have to work around for years to come. I find it's better to make these separate tables up front.


          For transactions like "invoice", I've found that it's important to consider up front what stages of the sales process this document represents. Sales and purchasing go through at least four common steps: quotation, order, fulfillment and request for payment. It's possible that your "invoice" represents all four stages happening at once, with one quote immediately becoming one order immediately becoming one shipment immediately becoming one invoice. Often, though, one realizes two late that two or more of these are distinct events, with their own attributes, and/or that stages can combine or split a previous stage, such as when two orders are shipped together, or one order gets shipped on two different dates. For more rambling thoughts on the matter, see here: http://extensitech.com/distribution-inventory-management/


          I don't have much to critique as far as your existing structure, which seems, though a bit simplified, logical.I only want to point out a couple of things that may be worth considering now since, in my experience, they tend to need consideration down the road as the solution grows and evolves, and some structural changes now can make your life easier later.



          Chris Cain


          • 2. Re: ERD Assistance/Guidance - CRM Database

            Thank you Chris Extensitech - do not hesitate to critique away. I am not a 100% newbie at FMP but my experience is in no way extensive!! The only way I can make sure I am doing the right thing is by either experimenting or listening to past experiences and knowledge from experienced developers like yourself.


            I am relieved to hear that the initial structure looks logical to you.


            I agree actually 100% to separate the companies and contacts and can see how it can be useful in the future.


            In terms of the invoices section the process works in a number of ways.

            1) Pro Forma (Order Confirmation) and deposit payment, Purchase from supplier (I cluster orders together within a period), fulfil that order and receive balance payment.

            2) Order Placed, I fulfil using stock already in my inventory at that time.

            There is generally not a quotation period for my business as all the prices/products are reviewed as an order is being placed. The confirmation is usually signed then and there also.


            I would like to full track the status of an order right from pro forma to fulfilment to payment. I have 3rd parties that are owed commissions on each sale - so I would therefore like to log when a payment was made, who it was made to and to therefore deduct it from the respective balances. (Customer Invoice Balance, Company to be received balance, commissions owed balance). An order is only considered closed once all balances are settled.


            On another note, I also need to dream up how I can extend the invoicing area to facilitate order returns / refunds / adjustments. I guess this information can all be kept in the invoice log and to add another range of statuses for refunds / order adjusted.


            My main concerns at the moment of which I cannot get my head around is the products, product size options and the barcodes assigned.

            - How do I structure the tables/relationships between a 'Product', 'Product Size Variation' e.g. small, medium, large.. and how to assign a barcode to a specific product and product variation?

            - Also, how do I structure 'Inventory Purchases' to accurately add quantity to a specific product and product variation - same question with Invoices too in terms of the invoice line items and the portal.

            - How do I ensure a barcode in the table of barcodes can only be assigned and used ONCE?