1

Guess I can use some help over here. I have split a products table into two tables, products and prices. This is the original table:

products:

+----+--------+-----------+-----------+-----------+
| ID |  sku   |    name   | price_old | price_new |
+----+--------+-----------+-----------+-----------+
| 10 |  1234  |   shirt   |   15.00   |   10.00   |
+----+--------+-----------+-----------+-----------+
| 11 |  1234  |   shirt   |   80.00   |   60.00   |
+----+--------+-----------+-----------+-----------+
| 12 |  5678  |   jeans   |   80.00   |   60.00   |
+----+--------+-----------+-----------+-----------+

To start, I first copied some information to the second table. So my structure at the moment is:

products:

+----+--------+-----------+-----------+-----------+
| ID |  sku   |    name   | price_old | price_new |
+----+--------+-----------+-----------+-----------+
| 10 |  1234  |   shirt   |   15.00   |   10.00   |
+----+--------+-----------+-----------+-----------+
| 11 |  1234  |   shirt   |   80.00   |   60.00   |
+----+--------+-----------+-----------+-----------+
| 12 |  5678  |   jeans   |   80.00   |   60.00   |
+----+--------+-----------+-----------+-----------+

prices:

+----+------------+-----------+-----------+
| ID | product_id | price_old | price_new |
+----+------------+-----------+-----------+
| 11 |     10     |   15.00   |   10.00   |
+----+------------+-----------+-----------+
| 12 |     11     |   80.00   |   60.00   |
+----+------------+-----------+-----------+
| 13 |     12     |   80.00   |   60.00   |
+----+------------+-----------+-----------+

But now the duplicates need to be deleted from products (all products with same name are duplicates), and their connections inside offers need to be connected to the first one. So this should become the end result:

products:

+----+--------+-----------+
| ID |  sku   |    name   | 
+----+--------+-----------+
| 10 |  1234  |   shirt   |
+----+--------+-----------+
| 12 |  5678  |   jeans   |
+----+--------+-----------+

prices:

+----+------------+-----------+-----------+
| ID | product_id | price_old | price_new |
+----+------------+-----------+-----------+
| 11 |     10     |   15.00   |   10.00   |
+----+------------+-----------+-----------+
| 12 |     10     |   80.00   |   60.00   |
+----+------------+-----------+-----------+
| 13 |     12     |   80.00   |   60.00   |
+----+------------+-----------+-----------+

As you can see ID 12 in prices, becomes connected to product_id 10 instead of 11 and de duplicate of 10 (which was 11) is deleted from products. It was a duplicate because it was the same product, only the prices were different. I would like to put the prices in a separate table and the rest of the product information inside a products table.

Anyone who can help me with a query?

3
  • A real-life type 3 SCD? el-oh-el. Update the prices table with the max ID for each product ( group by sku ), then delete the records in the products table that don't have the max ID.
    – Avarkx
    Nov 21 '14 at 14:10
  • Damn, ofcourse, you're right. Guess sometimes I think too hard... I'll try to create a query which does that. Actually it is not a type 3... cause I don't update the price_new and price_old :P I just got duplicated products, and I would like to merge them. I still have all historical data ;) I never save historical data in a single row :P – Nov 21 '14 at 14:31
  • That's true lol, it's what I really want to call a "type 5" right now for 2 + 3, falling just short of the 1 + 2 + 3 type 6 hybrid approach. I'm doing a sqlfiddle for what you're trying to do in the question, then a suggestion to just go to type 2 since you're changing the model anyway. There doesn't happen to be a timestamp you've omitted on the original table for simplicity's sake, is there?
    – Avarkx
    Nov 21 '14 at 14:37
2

So, as mentioned in the comments, since you apparently have the authority to modify the data model itself, there are perhaps better solutions available to you at this point. The direct answer to your question is to update your new prices table to reference your preferred products record in each duplication case, then purge the other duplicated products records. Following the logic outlined in the examples you've provided, the minimally valued key of each duplicated record can be used, as outlined in this SQL Fiddle. Specifically:

-- Update Prices set:
UPDATE  prices a
    SET product_id = b.NewID
FROM (  SELECT  c.ID, d.ID AS NewID
    FROM    products c
    INNER JOIN (SELECT  sku, MIN( ID ) AS ID
            FROM    products
            GROUP BY sku ) AS d
        ON c.sku = d.sku ) AS b
WHERE   a.product_id = b.ID;

-- Remove duplicates from Products:
DELETE
FROM    products
WHERE   ID NOT IN ( SELECT  MIN( ID ) AS ID
            FROM    products
            GROUP BY sku );

SELECT  ID, sku, name, price_old, price_new
FROM    products;

With that said, there is an opportunity here for you to move your model into a standard type-2 SCD. With your intended model, I see a real possibility of significantly over-complicated upsert logic being required, or redundant data being stored in the event of that trade-off being preferred. For instance, if the prices table is updated twice more for the 'shirt' record, say with $20.00 and then $10.00, you either end up with a record with an ID of 14 that first contains a NULL for the price_old, and then moves the price_new to price_old during the second update. Visually:

+----+------------+-----------+-----------+
| ID | product_id | price_old | price_new |
+----+------------+-----------+-----------+
| 12 |     10     |   80.00   |   60.00   |
+----+------------+-----------+-----------+
| 14 |     10     |    NULL   |   20.00   |
+----+------------+-----------+-----------+

+----+------------+-----------+-----------+
| ID | product_id | price_old | price_new |
+----+------------+-----------+-----------+
| 12 |     10     |   80.00   |   60.00   |
+----+------------+-----------+-----------+
| 14 |     10     |   20.00   |   10.00   |
+----+------------+-----------+-----------+

Actually typing that out makes me realize that the update logic still isn't even correct, since it breaks the natural historical chaining - instead of the NULL value, you'd actually be better suited to reach into the table for the most recent products record and use that record's price_new for the new record's price_old value. This brings me straight to the redundant data trade-off I'd mentioned earlier, resulting in an actual solution which appears to be a "worst of both worlds" scenario. Redundant version, quickly:

+----+------------+-----------+-----------+
| ID | product_id | price_old | price_new |
+----+------------+-----------+-----------+
| 12 |     10     |   80.00   |   60.00   |
+----+------------+-----------+-----------+
| 14 |     10     |   60.00   |   20.00   |
+----+------------+-----------+-----------+
| 15 |     10     |   20.00   |   10.00   |
+----+------------+-----------+-----------+

Hybrid redundant model with over-complicated upsert logic model:

+----+------------+-----------+-----------+
| ID | product_id | price_old | price_new |
+----+------------+-----------+-----------+
| 12 |     10     |   80.00   |   60.00   |
+----+------------+-----------+-----------+
| 14 |     10     |   60.00   |   20.00   |
+----+------------+-----------+-----------+

+----+------------+-----------+-----------+
| ID | product_id | price_old | price_new |
+----+------------+-----------+-----------+
| 12 |     10     |   80.00   |   60.00   |
+----+------------+-----------+-----------+
| 14 |     10     |   20.00   |   10.00   |
+----+------------+-----------+-----------+

Never mind, I take it back: The redundant method is the only way the history chaining doesn't get broken and redundant data is just another easily preventable problem waiting to happen. The moral of the story is to fix your data model now, while you still can.

@Pushpendra Rishi is correct in his answer about the sku being a natural key for the products table, but I am a sucker for surrogate keys and since there isn't any time stamp data provided in your scenario, just toss a unique index on that puppy and call it a day ( it should certainly at least have that ). We'll use the current primary key as the sequence it is to populate a type-2 SCD in my example, then never pretend using a system-generated surrogate key in that manner for business logic is okay again. We're getting you out of a jam, not trying to stick you in further, after all.

Here's the example in full. It's largely the same, except the new prices table is in the following format:

-- New Prices table:
CREATE TABLE prices
(
    ID          SERIAL PRIMARY KEY,
    product_id  INTEGER,
    price       DECIMAL( 18, 2 ),
    version     INTEGER
);

ALTER TABLE prices
ADD FOREIGN KEY ( product_id )
    REFERENCES products( ID );

CREATE UNIQUE INDEX UQ__prices__product_id__version ON prices( product_id, version );

And populating it is a bit different as well:

INSERT INTO prices( product_id, price, version )
SELECT  a.ID, a.price, ROW_NUMBER() OVER (
        PARTITION BY a.sku
        ORDER BY a.RowID, a.SubID ) AS version
FROM (  SELECT  ID, sku, price_new AS price, 1 AS SubID,
                ROW_NUMBER() OVER ( ORDER BY ID ) AS RowID
        FROM    products
        UNION ALL 
        SELECT  ID, sku, price_old AS price, 0 AS SubID,
                ROW_NUMBER() OVER ( ORDER BY ID ) AS RowID
        FROM    products ) AS a
ORDER BY a.RowID, a.SubID;

Then it should be relatively straight forward to use:

+----+------------+-------+---------+
| ID | product_id | price | version |
+----+------------+-------+---------+
| 25 |     10     | 15.00 |    1    |
+----+------------+-------+---------+
| 26 |     10     | 10.00 |    2    |
+----+------------+-------+---------+
| 27 |     10     | 80.00 |    3    |
+----+------------+-------+---------+
| 28 |     10     | 60.00 |    4    |
+----+------------+-------+---------+
| 31 |     10     | 20.00 |    5    |
+----+------------+-------+---------+
| 32 |     10     | 10.00 |    6    |
+----+------------+-------+---------+
1
  • Wow, awesome! I've executed your queries and so far everything looks very good! Products doesn't contain any duplicated data and some products return more rows when I execute a SELECT JOIN on the products and prices table :) :) I kept version away, cause the prices do contain timestamps. I just didn't mention that in my first post to keep things simple :) Nov 24 '14 at 13:43
2

Well, Apologies to all those SQL guru's - this might be a simplistic answer.

Since the product table will only have one record per product you don't need the ID column and can just use the 'sku' as the key.

Insert into that table a distinct list of the products e.g

INSERT INTO products (sku,ProductName)
SELECT DISTINCT (sku,ProductName)
FROM OldProductTable

That's your product table sorted.

How can you tell from your original data which price is the latest price? You have potentially two prices it could be. Lets presume the latest ID is the latest price.

Now you need to create a price table (as you have done) and create your foreign keys on it. You need some way of telling which is the current price though. One way is to give each record an iteration column, so the highest value in it is the latest price. Before you scream - this is a bit rubbish but is the only way I can see it fitting your data.

INSERT INTO ProductPrices (sku, PriceOld, PriceNew, iteration)
SELECT sku, PriceOld, PriceNew, ID
FROM OldProductTable

The primary key is sku and iteration. You want the latest price you just select the product and return the record with the highest iteration.

A better way to do it would be to have the concept of recording the price that was valid date range:

Product ID,Price,StartDate,EndDate

Live prices will not have an end date (null). When you want to change a price you simply update the end date and add a new record with the start date being the date the new price comes in to effect.

That's just me though! Be happy to see other peoples views.

1
  • Thanks for your help, but I want to keep the products table intact, because of the foreign keys with other tables. That's why Avarkx solution was the better option for me ;) Nov 24 '14 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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