I have a question regarding enforcing database integrity in a specific case. The business rules are described below, then the database design I derived from the business rules, and then the statement of the integrity problem I have with my design. I can think of four options for dealing with the integrity issue I see, which are detailed last.

Although I currently feel the fourth solution is the best I would welcome any input or advice that anyone can give me. I'm just not sure which solution is best in the sense of maintaining a normalized structure and avoiding redundancy while also maintaining appropriate integrity controls.

Business Rules
==============
1) The company has multiple warehouses identified by unique numbers.
2) The company uses parts which are identified by a unqiue part number.
3) For each part the company uses, there may be several individual sku's for that part, representing different colors or other options related to the part. These sku's are identified by a unique sku number.
4) The company may store the same sku in multiple warehouses.
5) The company purchases sku's from various suppliers.
6) A warehouse is supplied sku's of a product from the same supplier, but may work with multiple suppliers for different parts. (For example, if sku 1 of part 1 is purchased from supplier 1 then it must be assumed that sku 2 of part 1 would also be purchased from supplier 1. However, in a different warehouse part 1 and all sku's related to it might be purchased from a different supplier.
7) The company must track which parts are in each warehouse and who supplied that part.
8) The company must track which sku's of a part are in each warehouse, and how many.

This gives me the following entities:
Parts
Skus
Warehouses
Suppliers
WarehouseParts
WarehouseSkus

I see these being modeled like this:

Parts
-----
P# (PK)
Name
Desc

Skus
----
S# (PK)
P# (FK 1 references Parts.P#)
Price
Color

Warehouses
----------
W# (PK)
Name

Suppliers
---------
SUP# (PK)

WarehouseParts
-------------
W# (PK) (FK 1 references Warehouses.W#)
P# (PK) (FK 2 references Parts.P#)
SUP# (FK 3 references Suppliers.SUP#)

WarehouseSkus
------------
W# (PK) (FK 1 references Warehouses.W#)
S# (PK) (FK 2 references Skus.S#)
Inventory

So for a given sku within a warehouse there would be a row in the WarehouseParts table identifying the Warehouse in question, the part number the sku is associated with and the supplier that supplied that sku and all other skus associated with that part number. Additionally, there would be a row in the WarehouseSkus table indicating the warehouse and sku within the warehouse, as well as how many of that sku are in the warehouse (inventory).

The above design, works, but it is possible to insert a row in the WarehouseSkus table that is related to a part not present in the warehouse parts table, which is not what I want since if the sku exists in the warehouse, the part should have been supplied to that same warehouse.

Since the database is not able to enforce this constraint with the design above, I see only 4 options available to me:

Option 1) Do nothing and trust the application/stored procedures I write to properly maintain integrity. (Not acceptable since I may not control all access to these tables through my own stored procedures.)

Option 2) Use a trigger to verify that a WarehousePart row exists for any WarehouseSku record I try to insert or update. (This should work, but I would highly prefer a more declaritive solution).

Option 3) Change the primary key of the Skus table to a composite key of S# and P# and I will then be able to create foreign key constraints between WarehouseParts and WarehouseSkus. (This would work, but Skus.price and Skus.color would no longer be dependant upon the whole key, undesirable solution)

opt 3 ex:

Skus (modified for option 3)
----
S# (PK)
P# (PK) (FK 1 references Parts.P#)
Price
Color

WarehouseSkus (modified for option 3)
------------
W# (PK) (FK 1 references WarehouseParts.W#)
S# (PK) (FK 2 references Skus.S#)
P# (PK) (FK 2 references Skus.P#) (FK 1 references WarehouseParts.P#)
Inventory

Option 4) From the base design described at the beginning, Create an non-identifying relationship between the Skus table and the WarehouseSkus table with the columns S# and P#, and promote the S# column to be part of the WarehouseSkus primary key. Create another foreign key relationship between WarehouseParts and WarehouseSkus to make sure the W#, P# combination is valid for the given WarehouseSkus record. (I like this the most of all the solutions since it does not mess with the primary key on the Skus table, even though it brings along redundant information by propogating the P# to the WarehouseSkus table).

opt 4 ex:

Skus (modified for option 4)
----
S# (PK) (Part of Alternate Key 1)
P# (FK 1 references Parts.P#) (Part of Alternate Key 1)
Price
Color

WarehouseSkus (modified for option 4)
------------
W# (PK) (FK 1 references WarehouseParts.W#)
S# (PK) (FK 2 references Skus.S#)
P# (FK 2 references Skus.P#) (FK 1 references WarehouseParts.P#)
Inventory

Thank you for reading this entire post, I appreciate any advice you can give.

Option 4 is the way to go...

Most FK columns are considered to be forms of "controlled" redundancy anyway... I'd rather that than pointers or links anyday.....