Conditional Foreign Key Relationship

Question

I currently have a foreign key between two entities, and I would like to make that relation conditional to the entityType of one of the tables. Here's the hierachy of tables, this is done via FK refrences from child to parent

                  Store
            /                \
  Employees                    \
                             TransactionalStores
                            /       |         \
                     Kiosks         |          BrickMortars
                                 Onlines

I currently have a FK relation from Employee to store

ALTER TABLE Employees ADD CONSTRAINT Employee_Store
            FOREIGN KEY (TransStoreId)
            REFERENCES TransactionalStores(StoreId)

I would like to add the conditional:

WHERE TransactionalStores.storeType != 'ONLINE_TYPE'

Is this possible or must I subclass TransactionalStores into two new subTypes (e.g. PhysicalStores and VirtualStores)

Answer 1

Foreign keys can be made conditional...sort of. You don't show the layout of each table, so here is a typical design showing your relationships:

create table TransactionalStores(
    ID        int   not null auto_increment,
    StoreType char  not null,
    ..., -- other data
    constraint CK_TransStoreType check( StoreType in( 'B', 'K', 'O' )),
    constraint PK_TransactionalStores primary key( ID ),
    constraint UQ_TransStoreTypes unique( ID, StoreType ) -- for FK references
);
create table Kiosks(
    ID         int   not null,
    StoreType  char  not null,
    ..., -- other Kiosk data
    constraint CK_KioskStoreType check( StoreType = 'K' ), -- kiosks only
    constraint PK_Kiosks primary key( ID, StoreType ),
    constraint FK_Kiosks_TransStores foreign key( ID, StoreType )
        references TransactionalStores( ID, StoreType )
);

The Onlines and BrickMorters would have the same basic structure but with StoreType constrained to only 'O' or 'B' as appropriate.

Now you want a reference from another table to TransactionalStores (and through it to the various store tables) but limited to Kiosks and BrickMorter. The only difference would be in the constraint:

create table Employees(
    ID         int       not null,
    StoreID    int,
    StoreType  char,
    ..., -- other Employee data
    constraint PK_Employees primary key( ID ),
    constraint CK_Employees_StoreType check( coalesce( StoreType, 'X' ) <> 'O' )), -- Online not allowed
    constraint FK_Employees_TransStores foreign key( StoreID, StoreType )
        references TransactionalStores( ID, StoreType )
);

In this table, the FK reference forces StoreType to be either 'K', 'O' or 'B' but the field constraint further limits it to only 'K' or 'B'.

For illustration, I've used a check constraint to limit the store types in the TransactionStores table. In real life, a StoreTypes lookup table with StoreType being a FK to that table would probably be a better design choice.

Answer 2

A foreign key can't be made conditional so that is out of the question. The business rule appears to be that an employee can work for one and only one physical store. Given that, the super type of store has two sub-types as you suggested: Physical and Online. Each physical store may be staffed by one or more employees, and each employee must be assigned to one and only one physical store. Physical stores then have two sub-types, Brick and Mortar and Kiosk. Having three direct sub-types - Kiosk, Online, and Brick and Mortar - hides a property that is possessed by every store - whether or not it can be found at a physical location. Now the design relies on a human to understand the semantics inherent in the sub-type names to understand that online stores don't have employees. This is not readily apparent in the declared schema and code in the form of a trigger must be written to express that understanding in a way the DBMS can enforce. Developing, testing, and maintaining a trigger that does not impact performance is a much more difficult solution to implement as is shown in the book Applied Mathematics for Database Professionals.

Sub-typing Store first on its kind of location and then on the physical store's kind of structure is a more correct design with respect to the business rules and eliminates the need to write code to enforce the rule. Once the property is clearly included as a store location type which can be used as a discriminator for the sub-types, the relationship can be made between employees and physical stores directly and thus fully implementing the rule just with the foreign key constraint. ere is a data model created with Oracle SQL Developer Data Modeler that shows the super and sub-typing using Barker-Ellis box in box notation for super and sub-types, which I prefer for its elegant presentation. The diagram can now clearly show the rule as well.