Re: Modifying database schema without losing data

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

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

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.

Org(id, .... )
Person(id, person_org_id, person_site_id, ... )
Person_Org(id, org_id, person_id, ....)
Contact(id, person_id, address, city, state, zip , email, ....)
Site(id, name, address, .... )
Person_Site(id, person_id, site_id, ... )

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

On Mon, Sep 28, 2020 at 10:15 AM Rich Shepard <rshepard(at)appl-ecosys(dot)com>
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.
>
> Regards,
>
> Rich
>
>
>