First of all I know that there is no Car, Model, Manufacture... tables that I probably should make, but that is not a problem right now.

I must say that I have already made database and relational model looks like this (I simplified it, to concentrate on important), but I need to draw a conceptual (domain) model of this:

  • Client (clientID, name, surname, telephone)

  • Address (clientID, number, street, cityZipCode)

  • City (cityZipCode, cityName)

  • Visit (clientID, visitDate, totalPrice)

  • Service (serviceID, serviceName)

  • Criteria (criteriaName, size, measureUnit)

  • ServicePriceCriteria (serviceID, criteriaName, size, price)

  • VisitItem (clientID, visitDate, serviceID, criteriaName, size, quantity)

My the biggest problem was how to provide that ONE service can have different prices for different criteria (i.e., Oil Changes for the truck costs 15€. For suv same service costs 8€.... Other example is when price depends on the size of the wheel. For 14 inches wheel some service costs 10€ and the same service for 17 inches wheel costs 15€.)

So for the first example I have:

  • Service(123, 'Oil Changes'), Criteria('typeOfVehicle', 'Truck', null), ServicePriceCriteria(123, 'typeOfVehicle', 'Truck', 15)
  • Service(123, 'Oil Changes'), Criteria('typeOfVehicle', 'SUV', null),ServicePriceCriteria(123, 'typeOfVehicle', 'SUV', 8)

For the second example it looks like this:

  • Service(124, 'Some Service'), Criteria('Size of wheel', '14', inches), ServicePriceCriteria(124, 'Size of wheel', '14', 10)
  • Service(124, 'Some Service'), Criteria('Size of wheel', '17', inches), ServicePriceCriteria(124, 'Size of wheel', '17', 15)

I hope so you realized what was my idea. I wanna to ask you is this my solution acceptable at all? And is this correct domain model of this database: http://imageshack.us/photo/my-images/257/conceptual.png/


I think you are trying to be a bit too "elegant". Einstein is quoted as saying "things should be made as simple as possible, but no simpler."

You are trying to have a single record for something like an oil change - but then you need two other tables that give you the flexibility to apply the approriate price for this service in different situations.

Instead, I would suggest that you flatten out your three tables and just have a SKU table which includes a default price. You would have different SKU records for each kind of oil change, for example.

The service technician (i.e. user) needs to apply the proper SKU to each service they provide. This way, you are relying on the user to pick the right service/price rather than trying to build a table-driven set of business rules that must be used so that you can "deduce" the proper pricing. I would say, based on my experience, that users would much rather have a short list of SKUs to scan through and to pick from rather than having some kind of interactive expert system that asks them multiple questions about different criteria until the right answer finally pops out the bottom.

If you want to allow some drilling down (or some rolling up) for SKUs, you can create a grouping table that groups similar SKUs. If you really feel it necessary, you could even make this grouping hierarchical.

Your model might look something like this:

Simplified ERD

This will be easier to build and maintain, and much easier to explain to your users. I believe too, as I said before, that your users will find this to be easier to use at the service counter too.

  • Thank you for your advice. I think you are right when you say that I trying to be a too "elegant". I just wanna ask you do you think that domain model that I draw is correct for relational model? Once again thank you. – BojanSM Oct 19 '11 at 15:53
  • I think your domain model is fine until you get to the service price criteria. At that point I would flatten your model out so that visit item is pointing at a simplified view of services/products, each of which has its own price. This is similar to what I drew in my answer. Note that this will change the primary key of your visit item table. You might want to have a simple counter: line item #1, line item #2, etc. This will be easier to work with from the user/business end, since they will probably want printed invoices with item line numbers. – Joel Brown Oct 19 '11 at 22:00
  • @Joel - what tool did you use to draw the diagram? – Jack Douglas Dec 16 '11 at 22:27
  • The diagram is drawn with Visio. I created ERD smart shapes that follow James Martin's IE graphical conventions, which I find easy to explain to non-data modelers. The sketchy look is achieved by applying a wavy custom line pattern that I created. I use it for quick sketches and early models. The hand-drawn look helps to reinforce a sense of a design being preliminary. – Joel Brown Dec 17 '11 at 4:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.