How to design product/service schema if their are multiple sub-variations - exploding size

Thanks in advance for any advice given.

I’m looking to create a MySQL database that holds data of state, county, city-level lookup services. A user would contact the state/county location, provide details of what they’re looking for, and the document would be located, printed, and provided to the recipient for a fee.

An example of some lookups a location might provide:

  1. Land records
  2. Employment records
  3. Historic maps

Now, every lookup service may have one or more fees, such as:

  • $20 for the first copy
  • $10 for each additional copy
  • $10 extra for expedited service

If that were all I’m dealing with, it would be pretty simple, but the problem I’m facing is that each “location” is offering their lookup service in a variety of ways. There are basically four main “acquisiton methods”:

  1. in-person at a specific physical address
  2. by mail request
  3. by fax request
  4. online order form on their website

Not every state/county will provide all four options above.

And each of the above options have different fees and payment methods accepted, such as:

  1. in-person at a specific physical address
  • $20 for first copy
  • $10 for each additional copy
  • $10 extra for expedited service
  • cash or money order
  • visit physical address: 456 Acme Blvd, Phoenix, AZ 85001
  1. by mail request
  • $30 for first copy
  • $20 for each additional copy
  • expedited service not available
  • check or money order made payable to XYZ
  • mailing address: PO Box 00051, Phoenix, AZ 85002
  1. by fax request
  • $30 for first copy
  • $20 for each additional copy
  • expedited service not available
  • fax order request to: 555-555-5555
  • write in credit card number or photocopy of check on fax
  1. online order form on their website
  • $35 for first copy
  • $15 for each additional copy
  • expedited service not available
  • credit card or paypal
  • visit the following url to place order

I’ve been racking my brain trying to figure out how best to represent this in a relational schema and admin (CRUD) UI. As you can see, the cost, and payment method vary based on the acquisition method (e.g., in person, mail, etc).

While the physical address for all four methods are (probably) shared, that’s not guaranteed. For instance, the physical address for the “in-person” method (street) differs from the mailing address (po box) of the mail request option.

More attributes could be added to complicate it further, such as phone numbers or forms of identification required to order certain records.

I’ve thought of two possible ways to do this:

  1. Option 1: Create a global/master record that holds all shared attributes of the various acquisition methods. Then create a secondary record to override the master record. But with this approach, I’ll have to create a record for any minor deviation. I’ll also have to figure out if the overrides should actually override, merge, or exclude in addition to inheriting the attributes of the master record.

  2. Option 2: Just create a separate record type for every acquisition method: one for in-person, another for request by mail, etc. It doesn’t matter if all their attributes are the same. If all the fees, addresses, payment methods are the same. Just create separate records to keep them isolated and not dependent upon one another. No global/master record to look up to. So, if there were three different lookup services provided, I’d have to create a max of 12 records (3 services * 4 acquisition methods) per county/state.

This will result in a lot of duplicate data. I’l end up typing the same fees over and over. The same addresses and phone numbers over and over.

And it also feels stupid. Whether a product/service is offered in-person or by mail, the end result that’s delivered to the buyer is the same. There should be some sort of identifier that holds all these together, instead of them semi-arbitrarily drifting along.

So, this is basically a product within a product or an offer within an offer.

All my options seem horrible. Any guidance would be greatly appreciated.


This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.