0

I have an interesting situation where I am making a data base off someone else's design that has to be as rule complaint as possible.

diagram

The issue I have is with the section shown above as it is a sub-class super class situation with a mandatory, or where you are meant to have 2 table, however the phone being multi valued adds another table with issues. At the moment I have:

PersonalClient (ClientID, street, postcode, fName, lName, title, driversNum)
Primary Key ClientID

CompanyClient(ClientID, street, postcode, cName 
Primary Key ClientID                       
Foreign key Representative_clientID references PersonalClient(ClientID)

PersonalPhone (ClientID, phone)
Primary Key ClientID, phone         
Foreign Key ClientID references PersonalClient(ClientID)

CompanyPhone (ClientID, phone)
Primary Key ClientID, phone          
Foreign Key ClientID references CompanyClient(ClientID)

However I need to turn the phone tables into one table, this is where the issues is. How do I tell the phone table which client table to look at for the foreign key? I understand that a trigger or a create table as select is probably part of the answer. I am not adverse to creating additional tables that may store additional data but there must only be one 'phone table' with both the client id and phone number (one of the rules is as little as possible data duplication). I also can't use 2 columns and a view to merge the columns as is too hard to implement across the database. In addition it would be best if the solution worked across the whole data base and was not specific to the phone table.
I know this is a lot Thanks.

6
  • 1
    Why do you need 2/3 tables for phones? Why not just 1 table? Jun 3, 2019 at 13:22
  • Your scheme assumes one client can be a representative for a lot of companies, but one company can have one representative only?
    – Akina
    Jun 3, 2019 at 13:32
  • There are many issues with the overall design of the data base just ignore that. Although I guess there might be some situations where the different business or charities maeby use the same staff for some reason, or maeby a broker.
    – Unkown_h
    Jun 3, 2019 at 13:46
  • I guess rules wise there can be two phone tables, the issues is more with the rest of the database and the phone table situation was simply the simplest way to explain the question.
    – Unkown_h
    Jun 4, 2019 at 0:53
  • The reason that there can only be one rep is so that communication between the two companies is easy. It is mainly a security thing.
    – Unkown_h
    Jun 5, 2019 at 11:00

1 Answer 1

0

Client Table: Create a single "Client" table that includes attributes common to both PersonalClient and CompanyClient, along with a type indicator to differentiate between them.

CREATE TABLE Client (
    ClientID INT PRIMARY KEY,
    Type VARCHAR(10),  -- 'Personal' or 'Company'
    Street VARCHAR(255),
    Postcode VARCHAR(10),
    -- Other common attributes
);

PersonalClient and CompanyClient Tables: Create separate tables for PersonalClient and CompanyClient that reference the "Client" table.

CREATE TABLE PersonalClient (
    ClientID INT PRIMARY KEY,
    Title VARCHAR(50),
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DriversNum VARCHAR(20),
    FOREIGN KEY (ClientID) REFERENCES Client(ClientID)
);

CREATE TABLE CompanyClient (
    ClientID INT PRIMARY KEY,
    CompanyName VARCHAR(100),
    FOREIGN KEY (ClientID) REFERENCES Client(ClientID)
);

Phone Table: Create a single "Phone" table that references the "Client" table based on the "ClientID."

CREATE TABLE Phone (
    ClientID INT,
    Phone VARCHAR(20),
    PRIMARY KEY (ClientID, Phone),
    FOREIGN KEY (ClientID) REFERENCES Client(ClientID)
);

With this setup, the "Phone" table references the common "Client" table, and each specific client type (Personal or Company) has its own table with relevant attributes. This design ensures that you have a unified "Phone" table without duplicating phone numbers and maintains the relationship between phones and clients correctly.

To query for phone numbers associated with a specific client type, you can use joins between the "Client," "PersonalClient," and "CompanyClient" tables as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.