Table Design

[Nanette]

I have a Parts table and a PartDetails table and there are a few fields that
will have an initial data input into them and these fields should never
change. I then need to have a record of changes in those fields also.

I've thought of two ways to handle this.

1. In the Parts table have a field named Original Quantity. Then in the
Part Details table have another field named Updated Quantities.
OR
2. Is it better to put just one Quantity field in the Part Details table and
have it connected to the Part table?
OR
Is there an even better way to do this?

I'll eventually need to compare the orignial quantity with updated
quantities, and or pull up the most recently inputted quantity.

 

[John Vinson]

I have a Parts table and a PartDetails table and there are a few fields that
will have an initial data input into them and these fields should never
change. I then need to have a record of changes in those fields also.

I've thought of two ways to handle this.

1. In the Parts table have a field named Original Quantity. Then in the
Part Details table have another field named Updated Quantities.
OR
2. Is it better to put just one Quantity field in the Part Details table and
have it connected to the Part table?
OR
Is there an even better way to do this?

I'll eventually need to compare the orignial quantity with updated
quantities, and or pull up the most recently inputted quantity.

Might it make more sense to have a one-to-many relationship from the
Parts table to a PartHistory table? This could contain the PartID as a
link, a date, and a Quantity (as of that date). This would let you
retrieve not only the original quantity and the current quantity, but
the quantity at any point in time.

John W. Vinson[MVP]

 

[Nanette]

Hi John,

So, you are saying that I should have two tables. The Part table and a Part
History table with a one to many relationship, and then put the Quantity
field in the Part History table so that I'll be able to have the original
data as well as updates and retreive data from any dates?

 

[John Vinson]

Hi John,

So, you are saying that I should have two tables. The Part table and a Part
History table with a one to many relationship, and then put the Quantity
field in the Part History table so that I'll be able to have the original
data as well as updates and retreive data from any dates?

That's what I'm suggesting. Bear in mind I have NO knowledge of your
business rules, or even whether a history of quantities is something
you want.

John W. Vinson[MVP]

 

[Nanette]

Thanks John,

After considering your suggestion, I thought it was a great idea. I will be
needing to keep the history of quantities, as well as other data. I think
this is the answer I needed.