2

I'm trying to migrate lagacy data to a new eCommerce system which I'm building myself and I have full control over (so I can modify it however I want).

There is a product that came with a free gift for the first 100 customers. After that, it was just the regular product. Now the way that was handled in the old system was by just changing the product description and omitting the "with free gift" part after 100 sales. So from a database perspective, it was always the same product with the same id, just a different description.

However, I do have some options to distinguish them, and that's what I want to do while migrating. But I'm not sure how to represent something like that from a database modelling point of view. Should it be

  • 2 different products

which doesn't feel quite right, because it's not 2 different products, it's maybe 2 variations of it. Also, when creating reports, I don't want it to handle as 2 different products.

  • 1 product with 2 variants*, where the 2 variants are distinguished by the option whether it's with a gift or not

this also doesn't feel right, because 'free gift for the first 100' doesn't really sound like an option and variants shouldn't have different descriptions.

What could be another way to represent a temporarily promotion of a product in a structured and comprehensible way?

Edit

what exactly do you need to do with that information later?

I want to be able to

  • query how many products were sold with and without the gift, but still be able to treat that product as one, because the gift part doesn't really matter in reports
  • Be able to re-generate invoices with the same item description that existed when the item was purchased. The item description also should be available in multiple languages.

*The variants concept shortly explained: A product can have options (like a T-Shirt can have sizes and colors), and a variant is a product with a specific set of options (like a T-Shirt with size M in blue)

Improve this question
5
  • 3
    My first thought is that the price the product was sold for is a property of the transaction, not a property of the product. The product may have a recommended retail price, but the actual price during any given sale may be something else for all sorts of reasons. Do you store transactions in this database? And more importantly: However you choose to store this "free gift" status, what exactly do you need to do with that information later? Commented Sep 13, 2015 at 15:14
  • It reminds me of the composite pattern: a product that can be made of other products. You have the product "Item A" and the product "Item A offer", which in turns links to "Item A" and "Item B" Commented Sep 13, 2015 at 21:29
  • @Ixrec Yes, i do work with some sort of transactions (order and order items, where the price is a property of an order item). But the price is not the problem, it's actually the same in both cases. It's not the price change I'm concerned about, but the fact that it turned from "product with free gift" to "just the product" and I want to represent that change in a structured way somehow or just make clear that there were 2 kinds of the same product. About the information use part, I'll update my question. Commented Sep 14, 2015 at 7:11
  • @SJuan76 Yes, that sounds quite similar, but unfortunately it's not, because the free gift is not an item from our catalogue, so it doesn't exist in our database. In fact, it doesn't really matter what the gift was, I just want to make it clear that there was one. Commented Sep 14, 2015 at 7:18
  • To work with your existing structure, I would use the variant concept; it is just that the option for that variant is no longer available to choose. Commented Sep 16, 2015 at 7:23

1 Answer 1

Reset to default
1

Free gifts are promotional and inherently temporary yet recurring. I suggest you create a promotion/campaign table where you store discounts/gift details. When you do your transactions you store price (as mentioned by @Ixrec) and the promtionId applied to it. The promotion table should have start and end date for every campaign/promotion that is added. This will give you details about the discounts offered, re-running your invoice and other analytics

Improve this answer
1
  • Thank you, this is exactly what I went with. Promotions represent this concept perfectly! Commented Oct 22, 2015 at 9:04

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.