I am having problems in the relationship of my products and stock tables. The stock table is responsible for storing the quantity of each item in the products table and the date when the product came in stock. Each item in the products table can only be registered once in the stock table. So I was using the following structure:
create table produtos(
id_produto int unsigned auto_increment not null,
marca int unsigned not null,
categoria int unsigned not null,
nome varchar(100) not null,
valor double not null,
descricao varchar(250) not null,
tamanho varchar(2),
cor varchar(15),
sexo varchar(1),
constraint pk_produto primary key (id_produto),
constraint fk_marca foreign key (marca) references marcas(id_marca),
constraint fk_categoria foreign key(categoria) references categorias(id_categoria)
);
create table estoque(
id_estoque int unsigned auto_increment not null,
produto int unsigned not null unique,
qtd int unsigned not null,
datarep date not null,
constraint pk_estoque primary key(id_estoque),
constraint fk_produto foreign key(produto) references produtos(id_produto)
);
The problem is that in this way, I can register the same product several times in stock. So, I ended up thinking about some alternatives:
- Create a unique non-null field to identify the products in the stock table, and therefore prevent the product with the same code from being registered.
- Use the key product strangeness as the primary key of the stock table, but I have my doubts if this would be a good practice.
- Add the stock table in the product table through the Quantity and Reposition_Data attributes, for example. In that case, I would need to update the table with every purchase and arrival of a new stock.
What would be the best option for this case? If neither of them is good practice, what could I do? obs: I'm using mysql.