How to structure Tables with changing status without duplicating data

database-design

I have a table called Inquiry
it will contain many – Services, Commodities & Invoices.

Then later, You can change it to an Appointment which will contain the same Services, Commodities & Invoices of its Inquiry parent.

The problem is how do i model the relationship without duplicating data for Services, Commodities & Invoices that is already present in Inquiry table?

NOTE: Appointment could be created without creating an Inquiry.

Below is what i currently have in mind but i think its not flawless.

enter image description here

Best Answer

Assuming that your Inquiry table has a ID column, you can add a column in the Appointment table which accepts NULL and is a foreign key to the Inquiry table. Like so:

CREATE TABLE Appointment
(
    Inquiry INT NULL REFERENCES Inquiry(Id)
);

This way you can lookup the additional details when you need to without duplicating data which is a key component of obtaining third-normal-form.

EDIT

Because an Appointment can be created without an Inquiry I would recommend abstracting the Services, Commodities & Invoices to a separate table which has its own key, which you can then reference from both the Inquiry and Appointment like so:

CREATE TABLE OrderDetails
(
    ID INT NOT NULL PRIMARY KEY,
    -- other columns
);
CREATE TABLE Inquiry
(
   ID INT NOT NULL PRIMARY KEY,
   OrderDetail INT NULL 
       REFERENCES OrderDetails(Id)
);
CREATE TABLE Appointment 
(
    ID INT NOT NULL PRIMARY KEY,
    Inquiry INT NULL REFERENCES Inquiry(Id),
    OrderDetail INT NOT NULL REFERENCES OrderDetails(Id)
);

EDIT based on Comment

As you want to ensure that the Appointment will always have the same OrderDetail as the Inquiry you could change your model to this:

CREATE TABLE Inquiry
(
    Id INT NOT NULL PRIMARY KEY,
    -- other columns
);
CREATE TABLE OrderDetails
(
    Id INT NOT NULL PRIMARY KEY,
    Inquiry INT NULL REFERENCES Inquiry(Id)
);
CREATE TABLE Appointment
(
    Id INT NOT NULL PRIMARY KEY,
    OrderDetail INT NOT NULL,
    FOREIGN KEY (OrderDetail)
      REFERENCES OrderDetails(Id)
);

Your other alternative is to implement the check within the logic layer that creates the Appointment and ensure that it only ever creates it using the same Order Detail that the Inquiry has, if the Appointment was based off an Inquiry.

Related Question