AnsweredAssumed Answered

ERD Assistance/Guidance - CRM Database

Question asked by lcot17 on Feb 19, 2016
Latest reply on Feb 20, 2016 by lcot17

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)

 

Customers

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

 

Products

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.

 

Invoices

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.

 

Reports/Dashboard

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
T-ShirtFJDEE9395555501015.000.00'Checkbox'150.00

 

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!!

 

Thanks!

Luke

Outcomes