Matching Fields

Question

I am trying to create a database for products and product fitment. I'd like to be able to put length, width, and depth in a cell on two different tables, one for the product and one for the items it fits. Then, I'd like the table to auto-fill another field to match the SKU that is tied to the same dimensions.

So on Table 1:

A- Length

B - Width

C- Depth

D - SKU

Table 2:

A- Length

B - Width

C- Depth

D - SKU

If A B and C all line up with the same on each table, then D on table 1 automatically matches table 2.

Is this possible?

Answer 1

If Length, Width, and Depth constitute a candidate key of Table 1 (Products) the three columns of the same names in Table 2 (Items) constitute a composite foreign key.  In Products SKU is a legitimate column because it is determined by a candidate key.  The table is consequently normalized to Boyce Codd Normal Form (BCNF).

You refer to a product fitting items (plural).  SKU cannot therefore be a legitimate column in Items as it would be determined by the composite foreign key of Length, Width, and Depth.  The table would consequently not be normalized to Third Normal Form (3NF) as SKU is not determined solely by the whole of the primary key of the table.  It is therefore redundant, leaving the table at risk of update anomalies, and should be dropped from the Items table.  Its value for any row in Items  is always retrievable by an INNER JOIN of the two tables on Length, Width, and Depth.

Answer 2

Almost anything is possible. But I'm really not clear whether you are referring to a real table structure or a hypothetical.

If you have a products table and a table items the product fits, then you should have 2 tables with the following fields:

Length

Width

Depth

SKU.

Now if you want to find all the items a specific product fits into, I would create a form to select a product. I would then have a query like this:

SELECT * FROM fitment

WHERE Length = Forms!formname!Length AND Width = Forms!formname!Width AND Forms!formname!Depth;

Run that query and you will see all fitments that use the selected product.

Answer 3

Have you tried creating a query with the two tables and joining on the three fields?