Splitting a monolithic table into two related ones.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Splitting a monolithic table into two related ones.

Flakheart
I need to split a table into two as the current monolithic structure is incredibly wasteful but don't know how to do such a possibly complicated thing. I remember an analyser available years ago for MS Access database that did this but none found for SQLite.

My bloated table (Shoppinghistory) consists of the fields below, all of which are text except for 'record' which is an autoincrement field.

Creating tables is no problem (Grin).

Old table New item table New history table
(ShoppingHistory) (ItemData) (ItemHistory)
item Itemname ItemDataId (Points to Record in ItemData)
category Category
brand Brandname
manufacturer Manufacturer
boughtfrom Packaging
boughton Boughton
quantity Quantity
units Units
weight Weight
aisle Aisle
price Price
discount Discount
total Total
note Note
picture Picture
barcode Barcode
deleted Deleted
record Record Record

For each item in the original table (Shoppinghistory) that matches the new (ItemData) table on the "ItemName,Category,BrandName,Manufacturer" fields, the "boughton,quantity,aisle,price,discount,total data needs to be copied to the (ItemHistory) table with the (ItemDataId) column in the (ItemHistory) table containing the record number of the (ItemData) record it matched.

That way, when I query the (ItemData) table to see how many items were bought for that record and when, it will bring up matching records from the (ItemHistory) table.

Is there any simple way t do this or am I stuck with a monolithic table?
Reply | Threaded
Open this post in threaded view
|

Re: Splitting a monolithic table into two related ones.

Clemens Ladisch
Flakheart wrote:

> I need to split a table into two as the current monolithic structure is
> incredibly wasteful but don't know how to do such a possibly complicated
> thing. I remember an analyser available years ago for MS Access database
> that did this but none found for SQLite.
>
> My bloated table (Shoppinghistory) consists of the fields below, all of
> which are text except for 'record' which is an autoincrement field.
>
> Creating tables is no problem (Grin).
>
> Old table New item table New history table
> (ShoppingHistory) (ItemData) (ItemHistory)
> item Itemname ItemDataId (Points to Record in ItemData)
> category Category
> brand Brandname
> manufacturer Manufacturer
> boughtfrom Packaging
> boughton Boughton
> quantity Quantity
> units Units
> weight Weight
> aisle Aisle
> price Price
> discount Discount
> total Total
> note Note
> picture Picture
> barcode Barcode
> deleted Deleted
> record Record Record
>
> For each item in the original table (Shoppinghistory) that matches the new
> (ItemData) table on the "ItemName,Category,BrandName,Manufacturer" fields,

I am assuming the "Record" columns are declared as INTEGER PRIMARY KEY
so that they are autoincrementing.

  CREATE UNIQUE INDEX IName_Cat_BName_Manu ON ItemData(ItemName,Category,BrandName,Manufacturer);

  INSERT OR IGNORE INTO ItemData(
    Itemname, Category, Brandname, Manufacturer, Packaging, Units, Weight,
    Note, Picture, Barcode, Deleted)
  SELECT item, brand, manufacturer, boughtfrom, units, weight,
         note, picture, barcode, deleted
  FROM ShoppingHistory;

The "OR IGNORE" makes SQLite siltently ignore any records that would
violate the UNIQUE index.

> the "boughton,quantity,aisle,price,discount,total data needs to be copied to
> the (ItemHistory) table with the (ItemDataId) column in the (ItemHistory)
> table containing the record number of the (ItemData) record it matched.

The ItemData ID can be looked up with a correlated subquery:

  INSERT INTO ItemHistory(
    Boughton, Quantity, Aisle, Price, Discount, Total, ItemDataId)
  SELECT boughton, quantity, aisle, price, discount, total,
         (SELECT Record
          FROM ItemData
          WHERE Itemname     = ShoppingHistory.item
            AND Category     = ShoppingHistory.category
            AND Brandname    = ShoppingHistory.brand
            AND Manufacturer = ShoppingHistory.manufacturer)
  FROM ShoppingHistory;


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Splitting a monolithic table into two related ones.

Flakheart
First I must apologise for the dreadful formatting. I didn't realise my columns were broken when I copied and pasted the text until the message had been saved and I could not edit the post afterwards.

The ItemData ID can be looked up with a correlated subquery:

  INSERT INTO ItemHistory(
    Boughton, Quantity, Aisle, Price, Discount, Total, ItemDataId)
  SELECT boughton, quantity, aisle, price, discount, total,
         (SELECT Record
          FROM ItemData
          WHERE Itemname     = ShoppingHistory.item
            AND Category     = ShoppingHistory.category
            AND Brandname    = ShoppingHistory.brand
            AND Manufacturer = ShoppingHistory.manufacturer)
  FROM ShoppingHistory;

This last bit is amazing Clemens. I would never have figured it out. Very much behind the eight ball on more complex queries.

I now have nicely split table with 5,475 grocery items in one table with buying history and around 2,120 items with no history associated. Years of online grocery shopping allowed me to collect this data. None have UPC barcodes though, as I had to get the data from emails (Which didn't contain them). (That's the next job.)

Thank you so very much.




Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



If you reply to this email, your message will be added to the discussion below:
http://sqlite.1065341.n5.nabble.com/Splitting-a-monolithic-table-into-two-related-ones-tp70776p70802.html
To unsubscribe from Splitting a monolithic table into two related ones., click here.
NAML