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 <rshep...@appl-ecosys.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 > > >
