Posted by serge on 06/05/05 05:59

I am currently working on upgrading a production database
from an older version to a new version. I inherited the processes
of how the upgrade is done currently. In this case this part a manual
"record cleanup" is the process and I need to fix the records one by
one so I wanted to find a code-based upgrade method instead.

The tables in this case are sample tables i created to try to match
the real tables I am dealing with. I can't make any changes to any of the
database structure. I only need to "clean" it up and prepare for the upgrade
scripts that are already written previously.

So the logical questions of why the tables are like this is not something
I can do to change/update or correct.

You gave me an idea that i didn't think of. Maybe I can use
UPDATE CASCADE on the tables to do some of the update work.

Thanks


"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1117930866.608318.77020@o13g2000cwo.googlegroups.com...
> You have NULL-able columns for everything, no DRI to enforce the
> 1-to-many relationship you say is there and no relational keys
> (IDENTITY is an exposed physical locator and cannot be a key by
> definition). Why do keep saying "records", when a table has rows,
> which are completely different things?
>
> >> The goal is to update the name of the Product by giving it the name of
the first matching Name from ProductDetails <<
>
> How do you define this matching? Since tables have no ordering what
> does "first" mean? MIN()? That coudl be done with a UPDATE if you had
> real keys.
>
> Doesn't your industry have a standard part number system? Doesn't your
> company use it or have one of their own? The classic schema design
> for this problem usually looks more like this:
>
> CREATE TABLE Products
> (product_id INTEGER NOT NULL PRIMARY KEY, -- industry std?
> product_name VARCHAR (80) NOT NULL, -- really that long?
> tag_nbr INTEGER NOT NULL); -- no constraints?
>
> CREATE TABLE ProductDetails
> (product_id INTEGER NOT NULL
> REFERENCES Products (product_id)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> detail_name VARCHAR(80) NOT NULL,
> PRIMARY KEY (product_id, detail_name));
>
> CREATE TABLE ProductComponents
> (product_id INTEGER NOT NULL
> REFERENCES Products (product_id)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> component_name VARCHAR(80) NOT NULL,
> PRIMARY KEY (product_id, component_name));
>
> Begin by loading Products and get rid of the IDENTITY column, of
> course. Then load the other tables that reference it.

[Back to original message]