Two mysql table and 2 websites

[tonton]

Hi,
I have a method or conception/design problem

I have two tables for moment.

fist one using our warehouse
warehouse :

• id INT
• product_id INT
• quantity INT
• quantity_old INT
• modified TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
• created TIMESTAMP

This table is updated everyday (9 PM) by my script (warehouseUpdate.php)

And we have 3 websites.

For moment, we use only one for inventory
Second table for this website
storewebsite :

• id INT
• product_id INT
• sku varchar
• quantity INT
• quantity_old INT
• modified TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
• created TIMESTAMP

This table, storewebsite, is updated every day (8 PM) by my script (storeWebsiteUpdate.php) like this :
storeWebsiteUpdate.php search product_id of mySql table into the website’s product_id.
And then it compares les quantities : if the quantity is different, it takes (gets) quantity of web site and sets it on the mysql’s table (storewebsite).

The script strWebsiteUpdateByWarehouse.php (10 PM) checks the date of rectification by the warehouse table of field entitled ‘modified’. If the date is a new (today), it updates the table of storewebsite by quantity and etc.
Between 2 tables there are this relation / clause / criteria / foreign key relationships :
warehouse.product_id = storewebsite.sku

And at 11PM last script is rolled : to update website by the data came from table of storewebsite.

Until then, everything is fine It’s works.

Now we must add 2 websites. I don’t know how I can by a simple and effective idea.
I know I can add two others tables like : storewebsite2 and storewebsite3 using same script by differents parametres.
A fact, these sites do not have the SAME sku for that I have to add 2 tables. In other words, each site has its own rules on SKU

are there another way ? more moderne ?

Thanks

[chorn]

You can store the warehouse-id and set the primary key tow warehouse_id+sku.

[tonton]

How can I ?
I think that I need 5 tables ?
one table for warehouse

3 tables for website store :
storewebsite1
storewebsite2
storewebsite3

and one table for link / join between warehouse and storewebsite1,2,3
like this :

table link_join
id INT
warehouse_product_id INT
storewebsite1_product_id INT
storewebsite2_product_id INT
storewebsite3_product_id INT

or there are another solution ?

[astonecipher]

A simplification would be a single translation table. You have a primary key for the table, the warehouse sku, then columns for each warehouse. Then lookups are just a matter of cross referencing the websites sku to the universal warehouse id.

Though, I have to ask why you have a table for the website? It is redundant information. If you pull everything from the warehouse, there is no need to store another copy of it in the store table, unless they maintain different inventory, and even then, the warehouse could have a foreign key on the location of the item.