Single-column junction table - does it make sense?

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?

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.

StackExchange Database Administrators Q#209756, answer score: 4