Relationship 1:1

Question

I’m having problems in the relationship of my tables products and stock. The stock table is responsible for storing the quantity of each item in the products table and the date when the product went into stock. Each item in the product 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 this way, I can register the same product several times in stock. So, I ended up thinking about some alternatives:

• Create a field Unic not null to identify the products in the stock table, and consequently prevent the product with the same code to be registered.
• Use product key as primary key of stock table, but I have my doubts whether this would be a good practice.
• Join the stock table in the product table through the quantity and replenishment attributes, for example. In this case, I would need to update the table with each purchase and arrival of a new stock.

What would be the best option for this case? If none of them are good practice, what could I do? Obs: I’m using mysql.

Answer 1

In your case, that the relationship of the tables is 1 to 1, I do not see much sense in being implemented separately. It could be all in one table:

Produtos (id[PK], marca, categoria,..., qtd_estoque, data_estoque)

But if you are going to manage the individual items of each Product in stock, then you could have the stock table of the items and the quantity of products in stock could be computed from the table itself by summing the items of that product. Example of a stock table for items:

Estoque (id[PK], id_produto[FK], data_inclusao)