Обсуждение: Modifying database schema without losing data

I've been developing a business tracking application for my own use and it's
worked well up to now. But, I need to modify it by adding a table with
attributes from two other tables. I've not drawn a E-R diagram so I show the
two existing tables here:

CREATE TABLE Organizations (
   org_id serial PRIMARY KEY,
   org_name varchar(64) DEFAULT '??' NOT NULL,
   org_addr1 varchar(64),
   org_addr2 varchar(64),
   org_city varchar(16),
   state_code char(2),
   org_postcode varchar(10),
   org_country char(2) DEFAULT 'US' NOT NULL,
   main_phone varchar(16),
   org_fax varchar(12),
   org_url varchar(64),
   industry varchar(24) DEFAULT 'Other' NOT NULL
            REFERENCES industries(ind_name)
            ON UPDATE CASCADE
            ON DELETE RESTRICT,
   status varchar(20) DEFAULT 'Opportunity' NOT NULL
          REFERENCES statusTypes(stat_name)
          ON UPDATE CASCADE
          ON DELETE RESTRICT,
   comment text 
);

CREATE TABLE People (
   person_id serial PRIMARY KEY,
   lname varchar(15) NOT NULL,
   fname varchar(15) NOT NULL,
   job_title varchar(32),
   org_id int DEFAULT '0' NOT NULL
           REFERENCES Organizations(org_id)
           ON UPDATE CASCADE
           ON DELETE RESTRICT,
   site_name varchar(64),
   site_addr varchar(32),
   site_city varchar(16),
   state_code char(2),
   site_postcode varchar(10),
   site_country char(2) DEFAULT 'US' NOT NULL,
   direct_phone varchar(15),
   direct_fax varchar(15),
   cell_phone varchar(15),
   site_phone varchar(15),
   ext varchar(6),
   email varchar(64),
   active boolean DEFAULT TRUE NOT NULL,
   comment text
);

What I should have noticed when I designed this tool is that addresses and
phone/e-mail addresses can be duplicated when there's only a single
location. Now I have some prospective clients with multiple locations but I
have no names of individuals. So, I want to add a Location table with
addresses and contact information. Each row in that table will have a serial PK
and will use a FK to reference the Organization table. People will now
reference the Locations table rather than the Organization table.

There are data in each of these tables and my research in my books and on
the web have not provided any insights on how to modify the existing schema
and get date into their new appropriate table homes.

I think the long way is to dump the database and manually move rows (using
emacs) from their current table to the new one, as appropriate, but there're
probably much better ways to do this and I'm eager to learn.

Regards,

Rich

On 9/28/20 10:15 AM, Rich Shepard wrote:
> I've been developing a business tracking application for my own use and 
> it's
> worked well up to now. But, I need to modify it by adding a table with
> attributes from two other tables. I've not drawn a E-R diagram so I show 
> the
> two existing tables here:
> 
> CREATE TABLE Organizations (
>    org_id serial PRIMARY KEY,
>    org_name varchar(64) DEFAULT '??' NOT NULL,
>    org_addr1 varchar(64),
>    org_addr2 varchar(64),
>    org_city varchar(16),
>    state_code char(2),
>    org_postcode varchar(10),
>    org_country char(2) DEFAULT 'US' NOT NULL,
>    main_phone varchar(16),
>    org_fax varchar(12),
>    org_url varchar(64),
>    industry varchar(24) DEFAULT 'Other' NOT NULL
>             REFERENCES industries(ind_name)
>             ON UPDATE CASCADE
>             ON DELETE RESTRICT,
>    status varchar(20) DEFAULT 'Opportunity' NOT NULL
>           REFERENCES statusTypes(stat_name)
>           ON UPDATE CASCADE
>           ON DELETE RESTRICT,
>    comment text );
> 
> CREATE TABLE People (
>    person_id serial PRIMARY KEY,
>    lname varchar(15) NOT NULL,
>    fname varchar(15) NOT NULL,
>    job_title varchar(32),
>    org_id int DEFAULT '0' NOT NULL
>            REFERENCES Organizations(org_id)
>            ON UPDATE CASCADE
>            ON DELETE RESTRICT,
>    site_name varchar(64),
>    site_addr varchar(32),
>    site_city varchar(16),
>    state_code char(2),
>    site_postcode varchar(10),
>    site_country char(2) DEFAULT 'US' NOT NULL,
>    direct_phone varchar(15),
>    direct_fax varchar(15),
>    cell_phone varchar(15),
>    site_phone varchar(15),
>    ext varchar(6),
>    email varchar(64),
>    active boolean DEFAULT TRUE NOT NULL,
>    comment text
> );
> 
> What I should have noticed when I designed this tool is that addresses and
> phone/e-mail addresses can be duplicated when there's only a single
> location. Now I have some prospective clients with multiple locations but I
> have no names of individuals. So, I want to add a Location table with
> addresses and contact information. Each row in that table will have a 
> serial PK
> and will use a FK to reference the Organization table. People will now
> reference the Locations table rather than the Organization table.
> 
> There are data in each of these tables and my research in my books and on
> the web have not provided any insights on how to modify the existing schema
> and get date into their new appropriate table homes.
> 
> I think the long way is to dump the database and manually move rows (using
> emacs) from their current table to the new one, as appropriate, but 
> there're
> probably much better ways to do this and I'm eager to learn.

You could use INSERT INTO location(new_fields,) SELECT the_fields FROM 
the_table(s).

> 
> Regards,
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, 28 Sep 2020, Adrian Klaver wrote:

> You could use INSERT INTO location(new_fields,) SELECT the_fields FROM
> the_table(s).

Well, duh! I could have thought of that. That's exactly what I'll do:

Create the new table, move data from the old table into it, then drop
columns in the old table ... after checking all data's there.

Thanks, Adrian.

Stay well,

Rich

On Mon, 28 Sep 2020, Adam Scott wrote:

> What if a person is a member of more than one Org?  Consider a person_org
> table.

Adam,

Not applicable. An individual is employed by a single organization.

> I see mention of a site in the person table.  It may also be the case that
> you need a site table.

Yep. That's what I need to add.

> Often, you want a table for the Person and a Contact (or Address)  table
> separately. This allows for having more than one contact for a Person.

Possible, but I've not encountered more than a couple of phone numbers per
person (work and mobile). I don't need home addresses or phones.

> This way a person can be a member of more than one org, at one or  more
> sites, and have one or more contacts.

Doesn't apply to businesses in my market areas.

Thanks for the thoughts,

Rich

Rich Shepard <rshepard@appl-ecosys.com> writes:
> On Mon, 28 Sep 2020, Adam Scott wrote:
>> What if a person is a member of more than one Org?  Consider a person_org
>> table.

> Not applicable. An individual is employed by a single organization.

No part-timers in your universe?  (My friends in the restaurant business
would surely find the above pretty laughable.)

            regards, tom lane

On Mon, 28 Sep 2020, Tom Lane wrote:

> No part-timers in your universe? (My friends in the restaurant business
> would surely find the above pretty laughable.)

Tom,

Not in the markets I serve; at least, not at the environmental manager
level. I don't work for retail businesses; primarily natural resource
industries and others that require storm water discharge permits (no process
waters are allowed off-site when untreated in any state in the western US).

The only part-timers are external consultants hired for a specific purpose.

Stay well,

Rich