PostgreSQL / VisualStudio database modeling

0

Hello.

I have this form:

Asforthemodelingofthebankthereinthe"products" part, I intend to load all products (name, quantity available, unit value) and when the person select some and put the quantity will calculate the total.

My question is: Will the person select 5 products and save, how do I model in the database?

Because the person can search for the ID and should bring with them the products that have been selected.

The tables / forms that I have in the database are these:

CREATE TABLE CLIENTE(
    cod_cliente SERIAL PRIMARY KEY,
    nomeCliente VARCHAR(100),
    tipoPessoa INT,
    CNPJ INT,
    CPF INT,
    inscricaoEstadual INT,
    inscricaoMunicipal INT,
    CEP INT,
    endereco VARCHAR(100),
    numero VARCHAR(5),
    bairro VARCHAR(50),
    complemento VARCHAR(50),
    cidade VARCHAR(50),
    foneComercial INT,
    email VARCHAR(40),
    contato VARCHAR(20),
    celular INT,
    observacoes VARCHAR(240)
);

CREATE TABLE FORNECEDORES(
    cod_fornecedor SERIAL PRIMARY KEY,
    nomeFornecedor VARCHAR(100),
    tipoPessoa INT,
    CNPJ INT,
    CPF INT,
    razaoSocial VARCHAR(200),
    inscricaoEstadual INT,
    inscricaoMunicipal INT,
    CEP INT,
    endereco VARCHAR(100),
    numero VARCHAR(5),
    bairro VARCHAR(50),
    complemento VARCHAR(50),
    cidade VARCHAR(50),
    foneComercial INT,
    email VARCHAR(40),
    contato VARCHAR(20),
    celular INT,
    observacoes VARCHAR(240) 
);

CREATE TABLE PRODUTOS(
    cod_produto SERIAL PRIMARY KEY,
    nomeProduto VARCHAR(100),
    codigoProduto VARCHAR(20),
    valorVenda REAL,
    valorCusto REAL,
    disponivelEstoque INT,
    minimoEstoque INT,
    maximoEstoque INT,
    unidadeMedida INT,
    pesoLiquido REAL,
    pesoBruto REAL,
    cod_fornecedor INT REFERENCES FORNECEDORES(cod_fornecedor)
);

CREATE TABLE SERVICOS(
    cod_servico SERIAL PRIMARY KEY,
    nomeServico VARCHAR(100),
    tempoExecucao TIME,
    custoServico REAL
);

CREATE TABLE ORDEMSERVICO(
    cod_os SERIAL PRIMARY KEY,
    status INT,
    formaPagamento INT,
    responsavel INT,
    dataAbertura DATE,
    dataInicio DATE,
    dataPrevisao DATE,
    descricao VARCHAR(240),
    valorProdutosTotal REAL,
    acrDes INT,
    percentual REAL,
    valorTotalOS REAL,
    observacoes VARCHAR(240),

    cod_cliente INT REFERENCES CLIENTE(cod_cliente),
    cod_produto INT REFERENCES PRODUTO(cod_produto)

    /** incompleto **/
);
    
asked by anonymous 10.10.2017 / 17:49

1 answer

1

You need a sales table and a table of items from that sale ... simple ratio of 1: N cardinality where a sale can have multiple items, and an item can be in only one sale.

Sales chart:

id*|data|cliente|status|formapagamento|...|etc

Table of items:

item*|venda*fk|produto fk|quantidade|valor|...|etc

The columns with * are primary key, with fk, foreign key.

Note that in the item table, the primary key is composed using the sales id (which is also foreign) next to a sequential item.

I hope I have helped.

    
16.10.2017 / 12:46