I'm sorry for the vague question description, but I'm honestly a noob in DB design, as I've only really worked with small databases. This might change in the near future though. I have a spider which periodically crawls a website that sells merchandise. The objetive of this spider is to:
1. Obtain essential info from each object being sold, such as name, product_code, url, description, etc.
2. Get access to the price history of said object (by periodically rerunning the crawler), with respective timestamp.
These two requirements lead to two possible (in my limited db knowledge) database designs:
INSERTonly table called products, with no
UNIQUEfields (except row number), containing all fields mentioned above. Every repeated run of the crawler will duplicate existing rows.
On each product found by the crawler, one simple (and guaranteed) insert on the product table.
When consulting price history, simply group by product_code.
INSERTonly tables, one called products, containing all essential data (description, name, etc). This table also contains a
UNIQUEfield called product_code. The second table, called prices, contains only the fields product_code, timestamp and price. This table contains no
UNIQUEfields, except for the row id itself.
On each product by the crawler, two inserts:
- One simple and guaranteed insert on the prices table.
- An insert attempt, which will only actually go through when a never before inserted product is found.
When consulting price history, simply group by product_code on prices table.
I believe the second option will scale better in disk usage, as many fields will not be repeated needlessly (as in the first option).
The first option is probably faster though as it's simply one guaranteed
INSERT. How much faster though? How bad is it, in the second option, to try and constantly fail to insert?
EDIT - Note when considering the above point, thats in the second option the products table will be much smaller than the prices table.
Please go ahead and suggest other designs if you think it may be better! I'm completely welcome to discussion.