Preventing online shop without products while importing data

On my website several vendors can import their articles.

I want to give them the option to cleanup their articles (e.g. remove articles not in importfile).

The first thing I thought of is to just first delete all the articles of the vendor before import, however this might bring up a situation that a customer is visiting the site and don't see any products (of the specific vendor).

So after some more thinking I've came up with two other solutions:

1. import the products and remember what products were imported / updated (if they already existed) and delete the products of the vendor which weren't in the import afterwards
2. import the products in another temp table, remove the current products of the vendor and then copy the products in the temp table to the 'real' table.

However there mi开发者_StackOverflowght be some issues with both options.

1. is let's say remembering all imported / updated products really a good idea since sometimes it can be > 1 million products?
2. will it be possible to delete the current products and copy the products from the temp table to the real table be so fast that there is only a tiny chance that a visiting customer won't see any products?

Perhaps there are any more issues with the options I figured out.

Or perhaps there are some other options to do this?

PS

'Locking' the site / locking out customers while importing is not an option.

When I read your questions, two solutions came to my mind:

1. Fire up a "maintenance mode" while importing, but thats probably not what you want. Sorry didn't read your last statement.
2. Import (or delete) the items one by one, because that way the user would be missing at most one product at any given instant. There are a couple of caveats to consider here, though:
   • What happens if a product is deleted while it is inside the users shopping basket?
   • The references in already made purchases need to stay intact, in order to be able to reconstruct a bill after the product bought has been deleted.

Also, you can rename a table in PostreSQL like this:

ALTER TABLE TableB RENAME TO TableC;

In case you want to go with your "temporary table" solution, which I too have used, though under much less critical circumstances, admittedly. But note that, at least in MySQL InnoDB, you have to worry about foreign keys, which will still point to the old table if renamed, e.g. if there is a foreign key from TableA to TableB, and you rename TableB to TableB_old and TableB_new to TableB, than the foreign key will point to TableB_old. I don't know if this is the case with PostgreSQL though.

I have a variation on on of your suggested methods:
Have a table called vendortables setup that has columns for vendorname or id and the name of the table that lists their products. When a vendor wants to import, create a new table for the input (possibly vendorname + upload start time). Upload all of the data into it. Once that's done, grab the name of the current vendor's table (in vendortables) and update so the name of the new table is associated with the uploading vendor. Then delete the old table (that you grabbed before the update).
This way, no table data is every copied.

Example:

SELECT * FROM vendortables
+--+-----+
|id|table|
+--+-----+
|01|test |
+--+-----+

SELECT * FROM test
+--+----+
|id|name|
+--+----+
|01|car |
+--+----+


//Import & upload new data
CREATE TABLE test1 USING('?', 'new car')

SELECT * FROM test1
+--+--------+
|id|name    |
+--+--------+
|01|new car |
+--+--------+

//Phase in new data 
UPDATE vendortables SET table='test1' WHERE id='1'

//Delete old table
DROP TABLE test

And just have PHP ask vendortables for the name of the table responsible for displaying a vendor's products.

Also, since you mention your site's high usage, have you considered that caching will probably make requesting during an import very unlikely.

make a delete table

delete from del_vendor_article where vendorid='vendorid'

do this on article update

insert into del_vendor_article SET vendorid='vendorid', article_id='articleid'

possibly show them what will be deleted. then they can remove anything they might want to keep

SELECT item FROM articles WHERE NOT EXISTS (SELECT * FROM del_vendor_article WHERE articles.articles_id = del_vendor_article.articles_id);

delete not imported items

delete from articles WHERE NOT EXISTS (SELECT * FROM del_vendor_article WHERE articles.articles_id = del_vendor_article.articles_id);