Postgresql/Visualstudio database modeling

Question

Hello.

I have this form:

How much the modeling of the bank there in the part of "products" I intend to load all products(name, quantity available, unit value) and when the person selects some and puts the quantity will make the calculation of the total.

My question is: The person will select 5 products and record, as I need to model in the database?

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

The tables/forms I have in the bank 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 **/
);

Answer 1

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

Sales chart:

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

Item table:

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

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

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

I hope I’ve helped.