How to structure a db table to store ranges as 0; 20; 40; 60; 80 or 0; 50; 100; 150; 200; 250; & hellip; soon

advertisements

I want to store the below range in a table, the number of items in template01 is 6, second is 4. How can we structure the table to store this information. Displaying these values as comma separated or having one column for each range values stated would be my last option. Can you help on how to structure the table/tables to store the below said information.

Template01      0      10       20         30      40       50
Template02      0      50      100        150
Template03      0     100      200        300     400      500      600

Basically, I want to store these ranges as templates and use it later for saying if the luggage weight is from 0 - 10, it will cost $5 per Kg, if its 10 - 20, it will cost $4.8 per Kg etc

This is how the template will be used,

Domain: XYZ
Template01      0      10       20         30      40       50
Increment01%   125%    120%      115%       110%    105%    100%

Domain: ABC, am using or picking the same template 'Template01' but Increment%
will be different for different domain, hence

Template01      0      10       20         30      40       50
Increment02%   150%    140%      130%       120%    110%    100%

Idea is, I want to store Template of weight breaks and later I want to associate with different Increment%. Thus when the user chooses a weight break template, all the increment% values applicable or configured for that template can be shown for the user to choose one.

Template01      0      10       20         30      40       50
Increment01%   125%    120%      115%       110%    105%    100%  [Choice 1]
Increment02%   150%    140%      130%       120%    110%    100%  [Choice 2]


For a normalised schema you'd need to have tables for the Template, for the Increment, for the Template Weights, and for the Increment Factors.

 create table luggage_weight_template (
   luggage_weight_template_id number primary key,
   template_name varchar2(100) unique);

 create table luggage_increment (
   luggage_increment_id number primary key,
   increment_name varchar2(100),
   luggage_weight_template_id  references luggage_weight_template(luggage_weight_template_id)); 

 create table template_weight (
   template_weight_id number primary key,
   luggage_weight_template_id references luggage_weight_template(luggage_weight_template_id),
   weight_from  number not null);

 create table increment_factor (
   increment_factor number primary key,
   increment_id references luggage_increment(luggage_increment_id),
   template_weight_id references template_weight(template_weight_id));