How to implement product returns?

Question

I am having difficulties in implementing a requirement in database modeling, this requirement corresponds to product returns.

This is the scenario that occurs the returns and the different modes of return of products:

• Customer buys only one product and returns the same;
• Customer buys two or more products and returns only one of them;
• Customer buys a Kit that contains several products and returns the Kit whole;
• Customer buys a Kit that contains several products and returns some products of this kit;

I created an example to illustrate the modeling of the sales module, so that you understand the logic, follow the image:

The diagram above illustrates how this my database in relation to product sales, however I did not put all fields if it would not contain much information hindering understanding, but the logic is this, also I did not put the table of Salesman nor that of Client, but, both relate to the table of Sales.

You will need to register the following return information:

• Products developed or Kits;

• The customer responsible for the return;

• The seller to whom the customer has returned the product(s));

• The corresponding sale;

• Time and date of return;

The field id_kit on the table Item, may contain values 0 (null), this table may represent an Item as a product of sale or a Kit as a product.

With the above information how can I model the (s) table(s) corresponding to product returns?

Answer 1

I believe that the modeling of returns should be thought about the products, also because the customer can return a product from a kit today and after a few days return another product from the same kit. This way, when the customer returns an entire kit all products of the same must be registered as returned separately. Also for stock control. If the product does not have a kit in the return table the column will be null.

Table example:

Devotions

id_devolucao
id_item - table column ITEM who already owned the sales reference;
id_cliente - who returned;
id_vendedor - the seller who received the return;
id_produto - the returned product;
id_kit - if you have a kit;
dthr_devolucao