CODEWITHSUNDEEP
sqldatabase-designrelational-database
Gleb Skripnikov
Gleb Skripnikov

Reputation: 301

How to store editable copies of original data in a relational database?

Let's assume I have two entities: Company and Order.

Company:

Id
Name
Phone

Order:

Id
CompanyId
Name
TotalAmount

I simplified these entities, in the real project they are much more bigger.

In my application, I have a Companies view, where I can do basic CRUD operations. The same is for Orders.

When I create an order I must choose the company from my database. The problem is that I must allow the user to modify company data for each order, so it can be modified only for particular order.

I have 2 solutions:

First solution: Create a separate table OrderCompany where I can store copies of Companies data for each order. In this case, I can edit the copy and it will not affect the original company, but I have doubts because it will be a full copy of the Contacts table with only one additional field - OrderId.

Second solution: Store copies of companies directly inside the Companies table and add OrderId in this table. Original companies will be stored with OrderId = null, for copies OrderId will be assigned. In this case, I don't have 2 almost identical tables like in the first solution, but I'm not sure that storing originals and copies in the same table is a good idea.

Which of these two solutions is better in your opinion? Maybe there is a better way?

Upvotes: 0

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I suspect that you are confusing some different concepts. A "company" should not be changing. However, companies might have something -- say "contacts" -- that do change.

So, I think you want a model where you have:

  • companies, which has basic information about companies, such as companyid and company name and so on. This information does not change (or at least not per order). A company would also have a default "contact".
  • companyContacts, which would be for a contact in a company. This would have a companyId and then additional information. Different contacts might be used for different orders.
  • orders which would have a companyContactId.

Then the order would be assigned to a company and to a particular contact within the company. New contact information can be created as necessary,.

Upvotes: 1

vanshikamalhotra
vanshikamalhotra

Reputation: 69

I suppose we can go with your 1st approach as any change in the company table will not cost any changes in OrderCompany table.

Upvotes: 0

Related Questions