How to structure Tables with changing status without duplicating data

Question

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

• Mr.Brownstone: Is Appointment a different table?
• Junker: @Mr.Brownstone Yes please
• anon: Are Inquiry and Appointment separate things? Or separate stages of the same thing? Do you need to maintain the attributes for the inquiry, even if the appointment has different attributes?
• Junker: @AMtwo Are Inquiry and Appointment separate things? No. Separate stages of the same thing ? Yes. Do you need to maintain the attributes for the inquiry, even if the appointment has different attributes? No
• Mr.Brownstone: Edited my answer with additional information that should help.

Answer 1

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.

• Junker: Exactly what i have done. problem arises when you don't have Inquiry. check the note below
• Mr.Brownstone: Yeah I was just editing my answer - sorry I did not see the original note.
• indiri: That last edit works though I would also then remove the Inquiry id from Appointment. Appointment will always have an OrderDetails and that will reference the inquiry. Then there's no chance they point at different inquiries.
• Mr.Brownstone: Good point! I have edited that in.
• Junker: @Mr.Brownstone I think your last edit makes perfect sense I will mark it as the answer now.

Answer 2

Store the details in a third table. Inquiry and appointment can both reference the information and don't rely on each other. Appointment can still have a reference to inquiry in the event that they are related but if the appointment is made without an inquiry then it can be null.

CREATE TABLE inquiry
(
 inquiry_id int,
 info_details_id int
 --etc
)
CREATE TABLE appointment
(
 appointment_id int,
 info_details_id int,
 inquiry_id int --may be null
 --etc
)
CREATE TABLE info_details
(
 info_details_id int
 --services, commodities, and invoices, etc.
)

• Junker: This approach could leaves a vacuum where Inquiry and Appointment exists and they points to different info_details_id