Database Design – How to Structure and Connect a Price Table to Existing Relational Database

Such a table is often referred to as a link table or bridge table.

It is the standard way of creating a many-to-many relationship. With a direct foreign key between the two tables, you can only create a one-to-many relationship (because the primary key that the foreign key points at is also a unique constraint), or a one-to-one relationship (if the foreign key itself is also a unique constraint).

Depending on the DB design philosophy, the primary key column is often left out, and the primary key of the bridge table is composed of the two foreign key columns themselves (you need a unique constraint on these columns anyway, so why not make it the primary key?).

Instead of creating a separate table for each date, create a single details table and index by product id and effective date:

ProductDetails:
   productId 
   dateEffective 
   price
   ...

This way, you just insert a new row each time the details change.

insert into ProductDetails( productId, dateEffective, price )
values ( 1234567, '10/06/2014', 1.25 );

So you get the price based on the product id and the date; this way, you can get the current price for new orders, and get the price for past orders based on the date.

select price, dateEffective from ProductDetails where productId = 1234567 and
dateEffective between date1 and date2;

You really don't want to create a bunch of separate tables for different time periods, and you really don't want to keep adding columns to the Product table every time details update.