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 be2 different productswhich 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?Editwhat exactly do you need to do with that information later?I want to be able toquery 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 reportsBe 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)
Database Structure: A product that will only temporarily be available and then slightly change
database design;data structures
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