2

Let me preface this by saying I know I used the word "product" in the title despite this being about "services", I feel the concept and what I'm trying to implement is the same as the approach for product options and every one can relate easier than if I were to use "service options" in the title.

I'm building a database for my new auto repair business' website. I'm struggling with a way to store options for the various services I offer. For example:

A customer goes online and asks for the front brake calipers to be replaced. In this scenario the service is "Brake Caliper Replacement" and the service option is "Front". I store these in a table:

Services

| ID | Service Name              |
----------------------------------
| 1  | Brake Caliper Replacement |
| 2  | Oil Change                |

I have a second table that stores all of the potential options for each service and indicates if the option is required or optional. I use these fields on the site during the quote process to make sure that they pick one of the options that are required.

Service Options

| ID | Service_Id | Service Option | Required | Optional |
----------------------------------------------------------
| 1  | 1          | Front          | 1        | 0        |
| 2  | 1          | Rear           | 1        | 0        |
| 3  | 1          | Pad Replacement| 0        | 1        |

Now when they fill out the rest of the quote and select the service with the options they want, I'm struggling with how to store the relationships.

Here's how I currently have it setup:

Quote

| ID | Customer Id | Vehicle Id |
---------------------------------
| 1  | 1           | 2          |

Quote Service

| Quote Id | Service Id | Service Option Id |
---------------------------------------------
| 1        | 1          | 1                 |
| 1        | 1          | 3                 |
| 1        | 2          | null              |

Not all services will have required or optional options though. But I'm trying to determine the best way to store all of this data for generating quotes. Can anyone provide some assistance on if this design makes sense or perhaps a different way of looking at things that I may not have thought of?

user1347026
  • 588
  • 1
  • 8
  • 14
  • don't save the null last row. In fact they only want the front done. Re-jigger your first table shown – Drew Sep 23 '15 at 23:53
  • You could have a service option (eg. BASE) for all services that is required. – ergonaut Sep 23 '15 at 23:57
  • maybe such a service would be "lift hood" – Drew Sep 24 '15 at 00:03
  • I imagine the most flexible design is a self-joined table of service offerings, some required, some not, depending how each hangs under their parent. The table would have an int id `parentId` – Drew Sep 24 '15 at 00:12
  • I added the last row to show how I handle a service with no options selected. – user1347026 Sep 24 '15 at 00:23
  • right, but they are also not getting the glove comparent hinges oiled. The data does not belong in the db – Drew Sep 24 '15 at 00:25
  • I'm confused, it's a valid service, an oil change.. it they ask for a quote then why would it not belong in the DB? Again, this isn't real data this is just to illustrate the issues I'm trying to handle. – user1347026 Sep 24 '15 at 00:29
  • your last row of null does not belong in the db. It suggests that for every quote, you replicate all the service offerings under the quote id and plop a null in there, like, "nope they don't want it, but just wanted to tell ya" – Drew Sep 24 '15 at 00:32

1 Answers1

1

I imagine the most flexible design is a self-joined table of service offerings, some required, some not, depending how each hangs under its parent. It always for the most flexibility in subcategory levels in a hierarchy.

create table service
(   -- services (and sub-services) self-join hierarchy
    -- pricing naturally has no business in this table
    -- it must be kept high-level and generic enough to handle all autos
    -- from Hyundai to BMW
    serviceId int auto_increment primary key,
    description varchar(255) not null, -- the service name
    required int not null, -- 1 means required, 0 means optional
    parentId int not null -- 0 means no parent, otherwise serviceId of parent
);

It is even possible to have an Foreign Key Constraint in table service, but that would be for version 2.

Quote would have two columns: quoteId and serviceId

Drew
  • 24,851
  • 10
  • 43
  • 78
  • I like this idea, I guess I never thought of required options like Front and Rear as actual services, which I guess they technically are.So I would have 3 services for Brake Calipers, "Brake Caliper Replacement", "Front Brake Caliper Replacement", "Rear Brake Caliper Replacement". Is this what you're suggesting? – user1347026 Sep 24 '15 at 00:33
  • yes. because of the nuance for your trade, they are different. When they cease being different for all car types, consolidate them into 1 row – Drew Sep 24 '15 at 00:34
  • the trick is to keep as few rows as possible at first, so as not to have to consolidate them later into 1 id, with old id's floating out there that don't have a parent to reference (a.k.a. orphans). Foreign Key Constraints handle that for you, but leave the mess of services that aren't in force anymore – Drew Sep 24 '15 at 00:38
  • so it requires a decent amount of thought – Drew Sep 24 '15 at 00:38
  • I was considering a field that indicates if the service is currently offered or not. I really like this and it solves a bunch of problems I'm having now. It also removes some redundant data under the current design. – user1347026 Sep 24 '15 at 00:41
  • self-joins can be little tricky at first, often requiring a stored proc. I can forward on to you a few examples I wrote up. The main obstacle with using them is sheer size of data, but I don't think that would be your situation. Outside of that, they beat anything for flexibility of hanging hierarchies – Drew Sep 24 '15 at 00:43
  • That'd be terrific, thanks for your contribution, I'm going to implement this and see how it works with real data. – user1347026 Sep 24 '15 at 00:47
  • my pleasure. Glad to give an option. Check this out for a visual of something similar I wrote: http://stackoverflow.com/a/31967073 I wrote another one that deletes a hierachy below a `parentId`, and as you can imagine, re-hanging a branch is as simple as updating the parentId for a given service – Drew Sep 24 '15 at 00:56
  • Here is the one for [Delete a sub-hierarchy](http://stackoverflow.com/a/30744076) – Drew Sep 24 '15 at 01:03