I have been asked to take over a retiring colleague and to solve some problems of a previously developed solution.
The solution is complex but this particular Table is a big issue for me.
A PRODUCTS table is structured as follows:
Product1_details1 (list of values)
Product1_details2 (list of values)
Product1_details3 (list of values)
Product1_key (text calculated: Product1 & Details1 & Details2 & Details3)
Product2_details1 (list of values)
Product2_details2 (list of values)
Product2_details3 (list of values)
Product2_key (text calculated: Product2 & Details1 & Details2 & Details3)
The Table has some 300.000 records and a significant number of duplicates, i.e. different Clients may have purchased many Product1 and/or Product2 with various values in each of Details fields.
Of course my first observation was that the Table is not well structured and the funny aspect is that Product1 and Product2 might also have the same name and same details.
I have been asked to produce a portal which shows only unique values of Products and related Details.
I have already tagged duplicates adding a Serial Number as PrimaryKey, two new self join relationship.
PRODUCTS <-> PRODUCTS_key1
PRODUCTS <-> PRODUCTS_key2
and a new field If (PrimaryKey = PRODUCTS_key1::PrimaryKey or PrimaryKey = PRODUCTS_key2::PrimaryKey ; ""; "Duplicate")
I think I am “almost there” but I miss how to show a unique list of Product/Detail1/Detail2/Detail3
Before suggesting to totally destroy this Table and create a new one with correct structure I wonder if there is a faster solution to the problem.
Thanks for any help,