Data type (database) schema for complex service pricing

Need help
1

I am looking to design a DB structure/schema for our services that require too many dependencies in order to come up with the proper pricing. To simplify I want to know if my design is correct, simple, and the best way for optimization - DB structure is intended to have a waterfall structure.

1. Data Type: Cross-border
1.1 Data field: Cross-border Kind
1.2 Data field: Cross-border ID

2. Data Type: Service Kind
2.1 Data field: Cross-border ID
2.2 Data field: Service Kind
2.3 Data field: Service Kind ID

The succeeding levels will bear the IDs of the preceding levels in order to create a series of IDs merged together, which will be conditional in nature. When the condition is met, for example 1-2-3-4-5 (a consolidation of IDs of preceding levels), it will look up a certain Price Amount.

3

@mikeloc Hello Mike, tagging you in case you have any advice or you can share your wisdom about this setup? Thank you, appreciate you always.

4

Is it possible to just have a multiplier / addition on each ‘dependency’? Suppose dependency has ‘multiplyValue’ and ‘addValue’

Have a base price X
Dependency 1 - multiplyValue = 1.2
Dependency 2 - addValue = 500 (dollars)
Dependency 3 - multiplyValue = 1.5

Price = ((X x 1.2) + 500 ) x 1.5

Much easier to manage than a ‘waterfall structure’ which will become exponentially more difficult the more dependencies you add.

Also, sorry I’m not Mike but hope it helps.

5

You’re in the best of hands with George, and nobody should ever be sorry they aren’t me, George. Hell, even I don’t like being me. :wink:

6

The service we do is logistics. It has many variables or dependencies before it can create a the base price. I think this can be a multiplier group.

For example these are the variables or dependencies:

  1. Mode of transport - sea, air, land
  2. Border - international (outside country) or domestic (within country)
  3. kind of service - ex. sea freight, air freight, inland freight, etc.
  4. type of service - ex. if sea freight - is this full container, less than container load, etc.
  5. customer - which customer does the price apply to, so same services do not always have same prices for customers, some get lower, some get higher,e tc.

So based from this, I think in our actual design, we will have around at least 7-8 dependency group, this is at least.

7

This topic was automatically closed after 70 days. New replies are no longer allowed.