I have a common problem that I do not no how to solve in the most efficient way.
I have a table named Orders which has a PK named OrderID, and an FK named OrderInfoID which links to a PK named OrderInfoID in a table named OrderInfo. Basic as it comes.
OrderInfo has three fields besides OrderInfoID. Phone, Address, Dispatcher. When an order is placed, I want most values from the order form to go to the Orders table, but I want Phone, Address, and Dispatcher to go to the OrderInfo table, and the ID of this entry to go to the OrderInfo column of Orders. Still pretty straightforward.
However, I want uniqueness in the OrderInfo table. So if ALL three values, Phone, Address and Dispatcher, already exist in the table, I want to note the ID of that record, and use it to add the OrderInfo entry to the new Orders table record. If it does not exist, I want to add a record using the form values, and then add that record's ID to the OrderInfo column of the new Orders table record.
Hope I explained this clearly. Thanks.