How can I multi-org a single-org relational database?

Question

I currently have a db that contains suppliers for our organization. These suppliers can supply us with a variety of commodities (equipment, supplies, services, etc.). Just recently, our organization decided to expand this database to cover our other locations across the country. Now the db must account for a supplier that can supply to one or many locations/regions. The issue I am facing is that this supplier can supply the same or different commodities to our other locations. Currently the basic structure looks like this:

T_Suppliers

ID
Name
VendorCode

T_Commodities

ID
Commodity

T_SupplierCommodities

ID
SupplierID
CommodityID

T_SupplierProperties

SupplierID
PropertyID (this relates to a prop table not shown)

Many views I create are based on this T_SupplierCommodities table since it gives me the supplier and all of their commodities and vice versa. Should I just "denormalize" this and add another field to the T_Suppliers table that tells me the region (i.e. Region)? This would of course mean that many of the suppliers names will be repeated in this table with the only difference being the Region field (and perhaps their vendorcode which may or may not be different between regions.) The benefits of this approach is that I dont really have to change my queries that much since Im really just adding "new" suppliers.

-- OR --

Should I keep it normalized and create a table like this:

T_SupplierSubsidiaries

ID
SupplierID
VendorCode

*Note: I can put the actual region as a field in this table or a property in the T_SupplierProperties table.

This keeps things normalizes and allows me to have one supplier in the T_Suppliers table with many different subsidiaries (same company located in a different region that may or may not have the same vendorcode across regions.) This also requires me to change my T_SupplierCommodities table to this:

T_SupplierCommodities

ID
SSID (FK to the ID of the T_SupplierSubsidiaries)
CommodityID

I like this as it keeps things normalized, but it requires me to change many views and move data around. Of course I would also need to adjust my addresses table, phone table, etc. to account for the SSID instead of just a supplierid. Essentially this provides me with the ability to have a supplier "Joe's steel" to have three unique IDs (T_SupplierSubsidiaries) each with a region property (T_SupplierProperties) and each subsidiary with one or many commodities (T_SupplierCommodties). Which of my options is more efficient, or is there a more practical solution?

Answer 1

The trick here is to realise that the list of commodities no longer belongs to a supplier. Instead, the list of commodities belongs to the junction of supplier + region.

In other words, a list of commodities must now be identified by supplier AND region, rather than just supplier.