Database Design: Single-Column Junction Table Feasibility

database-design

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?

Best Answer

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.

Related Solutions

Sql-server – SQL Server Primary key / clustered index design decision

You are correct to separate "clustered index" from "primary key":

A clustered index is the organisation of data on disk is better if
- narrow
- numeric
- increasing (strictly monotonic)
The primary key identifies a row

Note: GUIDs make poor clustering keys

In this case, with the surrogate column, the table has 2 candidate keys:

ProductHistoryID
ProductNo + CreatedDateTime

Assumed convention states that the ProductHistoryID becomes the PK, but you can leave the PK on (ProductNo, CreatedDateTime): it will just be non-clustered. Which leads to indexes:

clustered index should be on ProductHistoryID
unique non-clustered index on (ProductNo, CreatedDateTime)

Example

CREATE TABLE Product (
    ProductHistoryID int NOT NULL IDENTITY (1,1) NOT NULL,
    ProductNo ...
    CreatedDateTime ...

then you a choice of

    CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductHistoryID)
    CONSTRAINT UQ_Product UNIQUE NONCLUSTERED (ProductHistoryID)

    CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductNo, CreatedDateTime)
    CONSTRAINT PK_Product UNIQUE CLUSTERED (ProductHistoryID)

Also, the pattern you have is a "type 2 Slowly Changing Dimension"

Sql-server – Do I need a separate Id column for this “mapping” table

If you have a one-to-many relationship between Producers and Products (in other words, a product can only belong to one producer), than it would make sense to just put a foreign key reference directly in your Products table:

One-To-Many

create table Producer ( id int identity(1, 1) not null primary key clustered, Name varchar(100) not null ) go create table Product ( id int identity(1, 1) not null, Name varchar(100) not null, ProducerId int not null foreign key references Producer(id) ) go

But if there's a chance that this will be a many-to-many relationship, then your best bet would be to use a Join table.

Many-To-Many

create table Producer
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table Product
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table ProductProducer
(
    ProductId int not null foreign key references Product(id),
    ProducerId int not null foreign key references Producer(id)
)
go

-- adding the primary key also ensures uniqueness
alter table ProductProducer
add constraint PK_ProductProducer 
primary key (ProductId, ProducerId)
go

If you decide to go with the Join table, you wouldn't need to have an additional key, as the combination of ProductId/ProducerId would ultimately be unique. You could use them as a composite key, so you wouldn't need that additional Id field in ProductProducer.

Best Answer

Related Solutions

Sql-server – SQL Server Primary key / clustered index design decision

Sql-server – Do I need a separate Id column for this “mapping” table

Related Question