Re: Foreign Key normalization question

On Tue, Sep 2, 2008 at 1:14 PM, Matthew Wilson <matt(at)tplus1(dot)com> wrote:
> I'm building an app that has a customers table, a locations table, a
> products table, and a product_locations table.
>
> They make a diamond shape.
>
> The locations table and the products table each have a customer_id
> column that links back to the customers table.
>
> Then the product_locations table table has just two columns: a
> location_id column and a product_id column, each linking back to the
> appropriate table.
>
> I want to write a constraint or a trigger or something else that makes
> sure that before a (location_id, product_id) tuple is inserted into the
> product_locations table, the system verifies that the product links to
> the same customer as the location.

If the two subordinate tables ALWAYS have to point to the same place,
why two tables? Can't a customer have > 1 location? I'm pretty sure
IBM has more than one corporate office you could ship things to.