troubleshooting Question

Changing tables to prevent anomolies.

Avatar of TheGoodOnesAreAllTaken
TheGoodOnesAreAllTaken asked on
Databases
10 Comments1 Solution385 ViewsLast Modified:
Major Edit
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:
Shipping Option - Country
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:
All tables with relationships
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.

Thanks.




Original Question:
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:


Country:
ID = Country1

Country:
ID = Country2

Shipping Option:
ID = ShippingOption1
Country ID = Country1

Address:
ID = Address1
Country ID = Country2

Order:
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?

Thank you.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros