Rebuilding-Relationship advice

August 1, 20178 yr

Hello forum. After upgrading (finally) to FM Adv 16, I've started a rebuild of my database. The current one has been in use for about 4 years, works really well for me (single user). I had a few things that weren't quite right in my mind, so I'd like to fix them with this rebuild.

This is a database for a heating oil delivery/commercial fueling/HVAC business.

The relationship question is about how to handle different types of customers, and their different attributes. Current design has all customers in one table, and therefore attributes that apply to one type don't apply to the other type. I figure it's not best to have 30+ fields in a table that apply to only one type, and 15 that apply to only the other type.

I also manage customers with a table for Interactions-email, newsletters, bill collecting.

The first type of customer is a Heating Oil delivery customer. Heating oil customers usually only have one location, with related equipment (one or more). The second type is a Commercial Customer. Commercial customers have related jobs in different locations.

Attributes that are similar:

-Name, delivery address, phone number, email address, etc.-basic info

Attributes that differ:

Heating Oil

-Tank Size

-Usage tracking (about 20 fields)

-Deliveries made (about 10 attributes)

-Equipment

-Service performed on the equipment.

-Automatic Delivery scheduling

-Invoicing for deliveries and service repairs & related products

-Interactive map to show location and nearby Heat customers.

Commercial

-Related table of jobs and sites (about 10 attributes-different from Heat customers).

-Deliveries made (about 5 attributes-different from Heat customers).

-Different interactive map to show location and nearby Commercial customers.

So the questions arise out of reporting and some of the questions I have are:

1. Should they be in 2 different tables? If so, I guess they would each need their own related tables for deliveries made, and for interactions.

a) Or would I connect them to the same tables as 2 different TOG's.

2. If they are 2 different tables, do I then create another TOG and attach both to an Invoicing Table. Then I would guess it would make reporting much easier as I could report on all deliveries from the Invoice table with sub-summaries by name and type of delivery. This is basically what I do currently, but am using QuickBooks. I'd like to create the reports all from FM as I can use the Interactions table to generate all needed correspondence.

3. Or, should I keep all customers in the same table and make one-to-one relationships of their unique attributes, reducing the number of fields in the main customer table.

a) If so, do I then go with anchoy bouy and multiple TOG'S?

Another thought would be for Commercial Customers, make one main record for each job, something like:

-Acme-South St. Philadelphia

-Acme-Sproul Rd. Broomall

Then by having a one-to-one of related attributes, reporting would appear to be easier

Attached is the graph for the current-in use DB

Well thanks for reading. If you need more detail, I'd be happy to supply.

Any thoughts, comments always appreciated.

Thanks

Steve.