Problem with setting up tables and relationship

[rhett7660]

Hello....

Here is what I am doing. It is an inventory database that also is an order tracking per se database.

When you are entering in the order, the top part is the vendor with an order number, date etc (will show table later).

The subform is the order details. This is the tricky part that I am having problems with.

In the subform I also need to have the unit that is placing the order for that particular item.

IE
product 1 pens black ball point unit=exams
product 2 pens red ball point unit=admin
product 3 paper legal color white unit=personnel


I can get it to work but the unit part is what is driving me nuts.

I am attaching a very stripped down version no queries etc....

Thanks

[rhett7660]

I think I figured it out.. I created a conjuction table with the following fields in it:

unitorderID
UnitID
ProductID

That seems to be working.....

[neileg]

Mmm....

Beware of a problem you might have with this structure. Any changes you make to the Product data will be applied to old orders as well as new. It is customary to create an Order Details table that is populated with the product data at the time of creation of the order so that subsequent changes are only applied to new orders. Strictly speaking this violates some of the rules of normalisation but is widely accepted as a pragmatic solution.

[Rabbie]

Originally Posted by neileg

Strictly speaking this violates some of the rules of normalisation but is widely accepted as a pragmatic solution.

From a philosophical view point this is not the breach of normalisation that it seems because regardless of future changes this is recording what actually happened. You do want for example for an order detail to be updated with a later price change as this would cause confusion.

In one of my databases I use a Price list table to provide default descriptions and prices but store the actual information in an order detail table.

[neileg]

Originally Posted by Rabbie

From a philosophical view point this is not the breach of normalisation that it seems because regardless of future changes this is recording what actually happened. You do want for example for an order detail to be updated with a later price change as this would cause confusion.

In one of my databases I use a Price list table to provide default descriptions and prices but store the actual information in an order detail table.

I don't disagree in practice. However there are sad souls who insist that the 'correct' approach involves setting dates on your standing data and retrieving the data based on the transaction date. Waste of time and resources in my mind.

[Rabbie]

I think in the real world it is what works that is important. Knowing when to stick to the rules and when to ignore them is the real art of successful development.

[rhett7660]

Hi all....

Thank you for the reply's.... I looked at both answers etc.... Can you tell me what other table(s) I would need to add.

I am still new at this, having a couple small databases under my belt, but this is by far been the most challenging.

Thanks
R~