0

I want to design data models of the following:
I have a Document object who has a client and DocumentLines.
Each line contains an Item and its quantity and price (See first row in the attached picture).
In the document I want to display the lines along with the items' part number, description, and date code.

enter image description here

Problem Time

So far, so good. I can use data relations to attach only the ID of the relevant object to relate it.
But! Let's imagine this scenraio

I have item A = { part_number: 'pn1', description: 'my item', date_code: 2015}, which has was instered into DocA.

When I enter DocA, I see the client had bought pn1 with the date code of 2015.
Now, when re-stocking this item, we get a newer date code of the item.
It is no longer 2015, but rather, 2016.
So I update my item: A = { part_number: 'pn1', description: 'my item', date_code: 2016}. Now, when I enter DocA, I see that the client had bought A with the date code of 2016!!
This is wrong! The details of the order should remain the same, and not be dynamically changed.

And also, what happens if I delete A? What will I see in DocA?

Question

How do I solve my puzzle, with one addition:
I need to allow changing item detail changes in documents.
Meaning, add something ad-hoc for the specific document (not line-comments).

What I came up with (which is the current solution), is duplicating the needed data in the DocumentLine object.
Though that seems so wrong, I can't come up with a better solution.

I'd like to hear your opinion.

Amir Tugi
  • 2,386
  • 3
  • 16
  • 18
  • Search/google re "historical data". – philipxy Oct 28 '16 at 21:37
  • Possible duplicate of [Can't decide whether normalization or de-normalization would work](http://stackoverflow.com/questions/40211539/cant-decide-whether-normalization-or-de-normalization-would-work) – philipxy Oct 28 '16 at 21:40

1 Answers1

2

As to the first part of the question, you show a good design. However, there is one attribute of Item that is volatile and you need to maintain the value as of the creation of the document. There are several ways to handle this. The easiest way is to copy the value into the DocumentLine table and populate it with the current value at the time the document line is created. So when the Item value changes, the copy retains the original value. This must be well documented so the people coming after you know what is happening.

The second part of the question is a little tougher. Is the part number and description also volatile? That doesn't seem right. Do you want the ability to change to a different item? You already have that. Just update to a new item number and quantity & price as needed. If it's something else you need, you'll have to give us a better idea of exactly what that is.

TommCatt
  • 5,498
  • 1
  • 13
  • 20
  • Yes, every field here is volatile, that is one of the demands of my client. I think that I will follow what you said, and what I have suggested in first place, and copy the volatile fields into the DocumentLine. I will also add a relation from `DocumentLine` to `Item` to allow watching the related item. As for the deletion part, I will add an `active` field to `item` so when deleted I will just hide it from the inventory. – Amir Tugi Oct 30 '16 at 07:07
  • If your client is at the point of specifying the nature of the fields of the table, you're in a heapa trouble. What the client really wants can better be expressed like, "A document consists of different items. These items start out as items from stock, but can change in time. This new information may or may not have anything to do with existing items in stock." That doesn't really make a lot of sense. Can you provide a plain language description of what is wanted? – TommCatt Oct 31 '16 at 05:22