I have rewritten the question and hopefully it is now clearer, which may prompt some answers.
A shop administrator creates various shipping options which are applied to various countries. So, the the Shipping Option and Country tables are simply:
When a customer creates an order, this creates an Order record, which tracks the address the order is going to, along with the shipping option chosen. However, the address table also has a country, so the complete table relationship is as follows:
The problem with this design is that the database blindly allows the Country ID of an Address record to be different to the Country ID of a Shipping Option, when clearly the countries of both the shipping address and the shipping option should be the same.
This could be fixed by the application (in this case a PHP application), however I'm wondering if there is a way of changing the designs of these tables so this situation would not be possible.
Below is a small part of a database design I have, however I'm struggling to keep the table design of it tidy and am wondering if there is perhaps a better design than I have.
* The administrator creates the shipping options for various countries.
* When an order comes in, an order record is created along with specifying the shipping option and the shipping option details if required.
The problems I see in this table design are as follows:
* The Shipping Option is tied to a Country, however so is the Shipping Address. This means that the database design right now allows for the possibility (which would be an error) for the Country of the selected shipping option to not match up to the country of the shipping address. For example:
ID = Country1
ID = Country2
ID = ShippingOption1
Country ID = Country1
ID = Address1
Country ID = Country2
ID = Order1
Shipping Address ID = Address1
Shipping Option ID = ShippingOption1
Notice that within the Order record, the country of the Shipping Address differs from the country of the shipping option.
* Certain shipping options may require further details if they are used - such as a tracking number; this is tied to each individual order. Again though, the shipping option may or may not require these details, yet it is quite possible for the database to not specify these required details.
I'm aware these issues can be 'fixed' with minimal effort within the application using the database, however I'm not happy about the design of the database having to be fixed and would much prefer these issues to not be possible within the database. So, how could this design be changed to eliminate these possible problems?