Create a freight calculation table in sql server 2012

Navigation
0

I have the following question and I need the help of the experts, so I can follow up on my project. I need to create a freight table just like the image below, but I do not know if I create a single table or create several tables, because then I have to join with my order table to calculate the freight.

This table has value by weight, state and city (if capital and interior)

The way I created the tables, I now have a doubt, how I will handle the calculation of the weight with and structure of tables below that I created, I created a state table, a tariff table to check if capital and interior , and the price list and how to check the weight also this and my doubt. Thank you. 

CREATE TABLE Estados
(
  Id INT,
  Sigla VARCHAR(2),
  CONSTRAINT PK_Estados PRIMARY KEY (Id, Sigla)
)

CREATE TABLE Tarifas
(
  Id INT PRIMARY KEY IDENTITY(1,1),
  Descricao VARCHAR(20)
)

CREATE TABLE TabelaPreco
(
  Id INT PRIMARY KEY IDENTITY(1,1),
  IdEstado INT,
  IdTarifa INT,
  Preco1 decimal default 0, --Faixa 5
  Preco2 decimal default 0, --Faixa 10
  Preco3 decimal default 0, --Faixa 15
  Preco4 decimal default 0, --Faixa 20
  Preco5 decimal default 0, --Faixa 30
  Preco6 decimal default 0, --Faixa 50
  Preco7 decimal default 0, --Faixa 75
  Preco8 decimal default 0, --Faixa 100
  PrecoAdicionalKG decimal default 0 --Preco Adional KG
)
    
asked by anonymous 08.12.2017 / 13:28

1 answer

0

- without sticking to PKs and FKs 

CREATE TABLE Estados
(
  Id INT,
  Sigla VARCHAR(2),
  CONSTRAINT PK_Estados PRIMARY KEY (Id, Sigla)
)

CREATE TABLE Tarifas
(
  Id INT PRIMARY KEY IDENTITY(1,1),
  Descricao VARCHAR(20)
)

CREATE TABLE Faixas
(
  Id INT PRIMARY KEY IDENTITY(1,1),
  Descricao VARCHAR(20)
)

CREATE TABLE TabelaPreco
(
  Id INT PRIMARY KEY IDENTITY(1,1),
  IdEstado INT,
  IdTarifa INT,
  IdFaixa INT,
  VigenciaDe DATE,
  VigenciaAte DATE,
  Preco  decimal default 0
  PrecoAdicionalKG decimal default 0 --Preco Adional KG
)

I also need to define where the "destination" information is, it change the freight value?

    
09.12.2017 / 13:12
Using Jparepository and pageable can I do a partial query? Error when trying to calculate the factorial, by parameter passage