-2

I am scraping products from a website. Each product has a price history table that contains a historical price with the associated date and store name. I would like to store this information in a database (it will be used to create graphs for each product). I was thinking of creating two tables. One would be a Product table with a product_id column [PK] and name column. The other would be a Price History table with a product_id column [FK], price column, date column, and store column. Is there a more efficient structure I could adopt?

2
  • 1
    No, that is the correct set of tables to use given the use case you've described.
    – AdamKG
    Commented Dec 28, 2021 at 15:28
  • 1
    More efficient from what perspective?
    – mustaccio
    Commented Dec 28, 2021 at 16:08

2 Answers 2

1

If you need a relational structure (you intend to query PRODUCT_HISTORY), then what you described is the way to go.

If you want a more document-oriented structure (you never query PRODUCT_HISTORY and only work with it as part of Product), then you can turn it into a structure like JSON and store it directly in PRODUCTS table.

1

Your setup is a good option to do it. If you need more information about the stores (store name, street address, city etc), you could add table store with needed fields and have store_id in history table a FK to it.

Don't use spaces and capital letters in table names, your main table name could be for example price_history

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.