Single-column junction table - does it make sense?

Question

I have inherited a database schema, which looks similar to the one below:

CREATE TABLE Products (
    ProductID int not null PRIMARY KEY,
    StoreGroupID int not null,
    -- product properties...
)

CREATE TABLE Stores (
    StoreID int not null PRIMARY KEY,
    StoreGroupID int not null,
    -- store properties...
)

The idea is that there's a 1-* correspondence between a product and a group of stores (a product is always carried by a single group of stores, and a group of stores can carry multiple products).

However, the current database does not define any sort of "group of stores" entity - the StoreGroupID is instead assigned by the business logic code from a sequence, completely arbitrarily and with no foreign key constraint.

Does it make sense to create a StoreGroup table, even if the only column it could carry would be the StoreGroupID? Or is there another way to model such a relationship?

Answer 1

What a great question, and not an uncommon situation of business logic in the app tier creeping into the db.

I consider this to be a subjective topic, so here's my two cents.

I would absolutely create a StoreGroup table. It allows you to define the foreign key, and maintain referential integrity in the database, as it was designed to do. It also ensures with guarantee, that the StoreGroupId is unique, should the app tier falter for whatever reason.

It also opens up the possibility of creating a surrogate key which can be beneficial in certain systems.

Yes, it only has one column (for now). But as we all now, this can change so easily and when/if this does you'll be tickled pink that you create a separate table.

Answer 2

In my mind I would separate the logical and physical design. Logically there is a entity type called StoreGroup. Physically, this entity type is not implemented as a table. This is OK. Often the physical design does not include all aspect of the logical design due to time constraints, priorities, performance or other reasons. Foreign key constraints, and the corresponding run-time checks, is a typical example of this. You've discovered another.

There is a cost, too. As you say, foreign keys cannot be enforced in the DB. Perhaps your application has other mechanisms to ensure consistency? As is so often the case there is a trade-off and the designer must make a judgement call.

Answer 3

It would allow you to define referential integrity, no invalid storegroups would enter the stores table. It's a kind of protection against programming errors, but may have other advantages.