I have a database with three tables, call them Sale, Store and Hub. Each instance of Store references an instance of Hub, although there may be instances of Hub not referenced in the Store table. In most cases, an instance of Sale will reference an instance of Store (and therefore can be mapped to an instance of Hub.) However in some cases a Sale will reference the Hub table directly.
This relationship models the following: stores are stocked by hubs (stores and hubs are both locations) and while most sales occur at stores, they can also occur at the hubs.
In a simplified version where the Sale-Hub relationship doesn't exist, I can easily make StoreID part of the primary key for the Sale table, which is what I'd like to do here, except that StoreID will occasionally be null (when the sale occurs at a hub and not at a store.)
Therefore a sample from the Sale table might look like this:
SaleID SaleDate StoreID HubID Quantity
1 2012-05-16 1 NULL 100
2 2014-09-27 2 NULL 99
3 2018-02-01 NULL 9 30
4 2019-11-12 17 NULL 207
The Store and Hub tables will include location info along with some store and hub specific fields.
The issue is how to handle creating a good alternate key for the Sale table (SaleID is my surrogate primary key.) In theory a sale should be determined by date and location. However I have two location fields and they are both nullable. I'm wondering if I can handle this using unique indexes? Or can I use check constraints somehow? Or an intermediate location table?
Also, as a follow up, suppose I also had a SuperHub table (imagine Hub is to SuperHub what Store is to Hub.) If I wanted to add a SuperHubID field to my Sale table, how would this affect things? With 3+ nullable fields don't check constraints/unique indexes start getting out of hand?
Here is potentially what an ERD for this could look like:
Edit: To clarify, I'm trying to figure out the best way to include location info as part of the alternate key for the Sale table, given that "location info" is split among several different tables (the primary key for the Sale table is just a surrogate key SaleID.) Trying to include all the location tables in the alternate key leads to Nulls appearing in the key (see sample from Sale table above.) What is the best way to deal with this issue?
Edit 2: Ok, here is some more clarification. I'm including table definitions, etc.
--Table Defs
CREATE TABLE Sale (
SaleID int NOT NULL IDENTITY(1, 1),
SaleDate date NOT NULL,
StoreID int NULL,
HubID int NULL,
Quantity int NOT NULL);
-- StoreID and HubID are nullable since a sale can occur at one or the other
-- Is there a better way to organize this?
-- @PeterHe mentioned Category/Subcategory model
CREATE TABLE Store (
StoreID int NOT NULL IDENTITY(1, 1),
StoreNumber int NOT NULL,
Customer nvarchar(20) NOT NULL,
Address nvarchar(50) NOT NULL,
HubID int NOT NULL);
CREATE TABLE Hub (
HubID int NOT NULL IDENTITY(1, 1),
HubNumber int NOT NULL,
Customer nvarchar(20) NOT NULL,
Address nvarchar(50) NOT NULL);
--PKs
ALTER TABLE Sale
ADD CONSTRAINT PK_Sale PRIMARY KEY CLUSTERED (SaleID);
ALTER TABLE Store
ADD CONSTRAINT PK_Store PRIMARY KEY CLUSTERED (StoreID);
ALTER TABLE Hub
ADD CONSTRAINT PK_Hub PRIMARY KEY CLUSTERED (HubID);
--FKs
ALTER TABLE Sale
ADD CONSTRAINT FK_Sale_Store
FOREIGN KEY (StoreID) REFERENCES Store (StoreID)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE Sale
ADD CONSTRAINT FK_Sale_Hub
FOREIGN KEY (HubID) REFERENCES Hub (HubID)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE Store
ADD CONSTRAINT FK_Store_Hub
FOREIGN KEY (HubID) REFERENCES Hub (HubID)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
--AKs
ALTER TABLE Store
ADD CONSTRAINT AK_Store UNIQUE (StoreNumber, Customer);
ALTER TABLE Hub
ADD CONSTRAINT AK_Hub UNIQUE (HubNumber, Customer);
ALTER TABLE Sale
ADD CONSTRAINT AK_Sale UNIQUE (SaleDate, StoreID, HubID);
--issue here is StoreID and HubID are nullable since sale could occur at either
