5

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?

3

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.

  • It also opens the possiblity to have additional columns in a StoreGroup table to help "descriptively" differentiate one StoreGroup from another. – Keeta Jun 15 '18 at 16:02
  • Agreed. But I think this is covered by the statement "... only has one column (for now)". – pim Jun 15 '18 at 16:18
  • 1
    In addition to other comments I think that if you ask enough subject matter experts, you will find that they have invented a StoreGroupName, even if its somewhat informal. – Walter Mitty Jun 16 '18 at 23:35
  • @WalterMitty Agreed! I did this in the most recent system I designed, purely so I could remember what certain things meant haha. – pim Jun 17 '18 at 11:46
4

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.

  • What do youmean with " foreign keys cannot be enforced in the DB"? – Gerard H. Pille Jun 15 '18 at 20:30
  • @GerardH.Pille What I mean is, if the StoreGroup table does not exists there is no unique list of StoreGroupIds that can act as the "referenced" end of a foreign key constraint. There will (likely) be many rows in both Products and Stores for each StoreGroupId so neither table can fulfill this purpose. – Michael Green Jun 16 '18 at 11:04
  • OK, now I follow you. – Gerard H. Pille Jun 16 '18 at 17:49
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.