Splitting two large de-normalised tables into multiple tables with link tables

Question

This is my first stackoverflow question so I'm very sorry if I haven't got the question-asking etiquette right.

I have two very messy large tables called Centres and Contacts - one has company and address data, and the other has contact, company and address data:

Centres:
-CompanyGUID (PK)
-CompanyName
-MainTelephone
-MainEmail
-Address1
-Address2
-Town
-Postcode
Notes

Contacts:
-ContactID (PK)
-FirstName
-LastName
-CompanyName
-Telephone
-Email
-Address1
-Address2
-Town
-Postcode
-Notes

I am trying to move this data into a new normalised database that has separate tables for contacts, companies and addresses and linking tables between each of these to allow for many-to-many relationships between all three tables:

Companies:
-CompanyGUID
-CompanyName
-MainTelephone
-MainEmail
-Notes

Contacts:
-FirstName
-LastName
-Telephone
-Email
-Notes

Addresses:
-Address1
-Address2
-Town
-Postcode

There are many more columns in the tables but this is enough to demonstrate the problem. Many of the companies and addresses are the same in both tables, but not necessarily.

I need to maintain the existing relationships between contacts, companies and addresses while removing the redundancy and allowing for many-to-many relationships between companies and addresses (companies_addresses link table) and contacts and companies (companies_contacts link table).

I have seen a few examples splitting one table into two destination tables but I have three, plus two link tables. Is this possible? what approach would you take?

many thanks in advance to anyone who can help.

Answer 1

I think what you propose with the five tables (companies, contacts, addresses, companies_addresses, companies_contacts) is just fine.

I wonder whether you really have many-to-many relationship between addresses and companies. The original table centres suggests only one (main?) address for a company. If that's the case, skip companies_addresses table and add a foreign key in table companies. On the other hand, in your data you might have the many-to-many relationship.

You may want to keep the association between contact and their address. (Perhaps you don't need this. I'm just speculating.) In that case, you will need a link table between companies_addresses and contacts table instead of companies_contacts table: the contact would be associated with a specific address and a company.

Hope this helps.

The SQL for this solution would be like this:

-- tables
-- Table addresses
CREATE TABLE addresses (
    addressId int  NOT NULL,
    address1 varchar(255)  NOT NULL,
    address2 varchar(255)  NOT NULL,
    town varchar(255)  NOT NULL,
    postcode varchar(255)  NOT NULL,
    CONSTRAINT addresses_pk PRIMARY KEY (addressId)
);

-- Table companies
CREATE TABLE companies (
    companyGUID int  NOT NULL,
    companyName varchar(255)  NOT NULL,
    CONSTRAINT companies_pk PRIMARY KEY (companyGUID)
);

-- Table companies_addresses
CREATE TABLE companies_addresses (
    companies_companyGUID int  NOT NULL,
    addresses_addressId int  NOT NULL,
    CONSTRAINT companies_addresses_pk PRIMARY KEY (companies_companyGUID,addresses_addressId)
);

-- Table contacts
CREATE TABLE contacts (
    contactID int  NOT NULL,
    firstName varchar(255)  NOT NULL,
    lastName varchar(255)  NOT NULL,
    CONSTRAINT contacts_pk PRIMARY KEY (contactID)
);

-- Table contacts_companies_addresses
CREATE TABLE contacts_companies_addresses (
    contacts_contactID int  NOT NULL,
    companies_addresses_companies_companyGUID int  NOT NULL,
    companies_addresses_addresses_addressId int  NOT NULL,
    CONSTRAINT contacts_companies_addresses_pk PRIMARY KEY (contacts_contactID,companies_addresses_companies_companyGUID,companies_addresses_addresses_addressId)
);


-- foreign keys
-- Reference:  Table_5_contacts (table: contacts_companies_addresses)


ALTER TABLE contacts_companies_addresses ADD CONSTRAINT Table_5_contacts FOREIGN KEY Table_5_contacts (contacts_contactID)
    REFERENCES contacts (contactID);
-- Reference:  companies_addresses_addresses (table: companies_addresses)


ALTER TABLE companies_addresses ADD CONSTRAINT companies_addresses_addresses FOREIGN KEY companies_addresses_addresses (addresses_addressId)
    REFERENCES addresses (addressId);
-- Reference:  companies_addresses_companies (table: companies_addresses)


ALTER TABLE companies_addresses ADD CONSTRAINT companies_addresses_companies FOREIGN KEY companies_addresses_companies (companies_companyGUID)
    REFERENCES companies (companyGUID);
-- Reference:  contact_companies_addresses (table: contacts_companies_addresses)


ALTER TABLE contacts_companies_addresses ADD CONSTRAINT contact_companies_addresses FOREIGN KEY contact_companies_addresses (companies_addresses_companies_companyGUID,companies_addresses_addresses_addressId)
    REFERENCES companies_addresses (companies_companyGUID,addresses_addressId);