Database design to avoid duplicated field without making it as primary key
During my database design, I have two tables (Product table and DatePrice table), with
In my ProductTable
Product ID / Product Name / Foreign Key to DatePrice table
In my DatePriceTable
Primary ID/ Date/ Price
The reasons to have the above table, is to record out the pricing of a product, which its price can be chanced along the time
2 June 2007 - Price of a Capucino is $10
3 July 2007 - Price of a Capucino is $12
3 August 2007 - Price of a Capucino is $13
So, In my ProductTable, it keep track the latest price of Capucino
Product ID / Product Name / Foreign Key to DatePrice table
1 Capucino3
Primary ID / Date / Price
12 June 2007 $10
23 July 2007 $12
33 August 2007 $13
Now, the user realize that the price of capucino only change to $13, during time 1st August 2007. So, the user will perform modification on 2nd col, 3rd row.
However, I do want to prevent user from accidently chance "3 August 2007" to "3 July 2007" since there is already such date in the 2nd row.
Perhaps I can have Date as the primary key. However, user will no longer able to perform modification on the Date.
So, is there any way I can prevent user from accidently enter duplicated date (without help of application logic)?
Thank you.
cheok
