What is the benefit of two FK, when one of them can be deduced from another table?

Question

The third party Microsoft SQL Server database I'm working on is using the structure which can be illustrated with this example:

Let's take three tables:

• Shop which is the topmost level and corresponds, for example, to an e-commerce website, given that several websites are using the same database,

• Category which is a logical category of products within a Shop,

• Product which belongs to a Category (given that the category is mandatory, so there should be no products which don't belong to any category).

Category has one foreign key to Shop. The Product has two foreign keys: one to Category, another to Shop.

If I were designing a similar database, I would have put only one foreign key to Product, linking it only to a Category. IMO, it:

1. Simplifies the schema,

2. Avoids the risk of inconsistent state, where product 1 belongs to category 1 and site 2, but category 1 itself belongs to site 1,

3. (Avoids redundant information to waste the place in the database),

4. Doesn't make it particularly more difficult to query data. Even if there are cases (for example a search) where the website would need products without carrying too much about the categories, but still considering to what shop they belong, making two joins instead of one is not a big deal.

Why was this third party database done in the way it's done? Are there benefits from this approach?

Answer 1

Category has one foreign key to Shop. The Product has two foreign keys: one to Category, another to Shop.

There should be a single foreign key referencing two columns in categories.

create table products (
  ...
  primary key (shop_id, category_id, product_id),
  foreign key (shop_id, category_id) 
    references categories (shop_id, category_id)
);

Think about it this way. Conceptually, normalization starts with a single relation that contains all the attributes.

shop_name  category_name    product_name
--
Wibble     Chain saws       Stihl 350
Wibble     Chain saws       Poulan 3X
Wibble     Pole saws        Black & Decker 14 foot electric
Wibble     Pole saws        Corona 15 foot

Thursby    Pole saws        Hitachi electric
Thursby    Pole saws        Remington electric
Thursby    Chain saws       Husqvarna 460
Thursby    Chain saws       Poulan 3x

It should be clear that the only candidate key is {shop_name, category_name, product_name}. It's in 5NF. There's no redundant data.

Replacing text with ID numbers won't improve that.

A design that requires you to chase ID numbers through a hierarchy of tables is an anti-pattern. It's like building an IMS database in SQL. (IMS was a problem the relational model intended to solve.)

Answer 2

Well it constrains you from the off

A large store may contain several categories of product

A product (say sporting goods) may exist in several categories clothing, sportswear

the facet/tag model is probably more applicable lest you wish to constrain the design

so you really should define your semantics ( language meanings ) before embarking on the design.

the simple model is one shop contains many categories a category contains many products

but in reality you may find shops to products will be many to many as will categories to products

Answer 3

Given that shops have a one-to-many relationship with categories, and categories have a one-to-many relationship to products, the second foreign key is unnecessary and allows inconsistency.

As you've noted, the relationship between shops and items is already defined via the other foreign keys. Storing the shop key on items means that the item can be allocated to one shop via its category, and another via its own shop key. Therefore in this example, the shop key should not appear on the item. If the item needs to be allocated to a new shop, this should be done by updating the category.

As has been pointed out, in many cases it would make sense to have shops related to categories in a many-to-many relationship, sharing data across shops. This would change things, but that's not the case here.