Mysql – How should an object that can reference an object of multiple types be modeled in a relational schema

Start with:

1. Create a ChartOfAccounts table with the Account code as Primary Key.
2. Add a Foreign Key constraint to ChartOfAccounts on all tables with an AccountCode field.
3. Use an IsDebit field, not the numeric sign, to distinguish Debits from Credits and reserve negative signs for transaction reversals (if used at all). This is necessary in order to generate T-Balances and Trial Balances properly from your Journal and Ledger
4. Create a Journal table with Primary Key: TransactionType, PostingDate, Account, SubledgerCode, IsDebit and minimum attributes of: Amount, CreatedDate, CreatedBy, DocumentReference
5. Design and spec a stored procedure (or type of routine suitable for where your business logic is located) for each type of transaction to be handled by the system. For your system these might be:
   • Ticket Purchase for Cash
   • Ticket Purchase on Account
   • Prize Payout in Cash
   • etc.

Please note that I am CGA, CPA in addition to being primarily a professional developer.

Update - Terminology:

• A Journal is a chronological list of the details of all transactions of a given type, such as Cash Receipts, Cash Disbursements, Sales, etc.
• A Ledger is a listing By Account of the aggregates of all transactions in a given time period.

It is occasionally necessary or expedient, when a wide variety of transaction types will be supported by the system (or to increase parallelism, as when many clerks need to be working at once Bob Cratchit style), to have multiple Journal files with different structure.

In a modern SQL Server system with only one Journal the Ledger could be defined as an Indexed View on the Journal. This would eliminate the need for either a trigger on the Journal to update the Ledger, or a batch-processing design.

Also, it is acceptable to have separate DrAmount and CrAmount columns in place of an IsDebit flag and single Amount column.

Assuming that the app will be treating these various shop-types in the same manner for some operations (operations that do not care what type of shop that it is), then my preferred approach is to use a subclass / inheritance model. I have detailed this out in a couple of other answers here:

• Don't know how to transform variable entity into relational table

• Can't convert this type of ER diagram into SQL or relational tables

The basic concept would be as follows (using Microsoft SQL Server T-SQL semantics).

First you need the main entity table. It represents various "types" so those need to be defined in a lookup table (and the lookup table should have a matching enum in the app layer). Please note that I am using a TINYINT here which holds values from 0 to 255. Use the smaller numeric type that will meet your needs.

CREATE TABLE dbo.ShopType
(
  ShopTypeID TINYINT NOT NULL PRIMARY KEY, -- NO IDENTITY / auto-increment!
  ShopType   VARCHAR(50) NOT NULL
);

CREATE TABLE dbo.Shop
(
  ShopID     INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  ShopTypeID TINYINT NOT NULL REFERENCES dbo.ShopType (ShopTypeID), -- FK to ShopType
  ShopName   NVARCHAR(100) NOT NULL,
  VisitorsLastWeek INT NOT NULL DEFAULT (0),
  -- {OtherCommonFields},...
  CreateDate DATETIME NOT NULL DEFAULT (GETDATE()), -- or GETUTCDATE()
  ModifiedDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()
);

Next you create a subclass table for each particular "type". It is sometimes appropriate to share a subclass table for multiple "types", but just be careful when doing that so one type doesn't evolve differently than the other type(s) that might be sharing the same subclass table. Please note that the PK of the subclass tables is the same field as the PK in the parent class (i.e. the [Shop] table) and even FKs to it. The reason is that the row in both tables (e.g. [Shop] and [ShopRepair] that have the same value for [ShopID] are together a complete entity; the row in the subclass table does not represent anything by itself and doesn't need a separate ID.

CREATE TABLE dbo.ShopFood
(
  ShopID INT NOT NULL PRIMARY KEY REFERENCES dbo.Shop(ShopID),
  MostPopularItem  NVARCHAR(50) NULL,
  ModifiedDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()
);

CREATE TABLE dbo.ShopRepair
(
  ShopID INT NOT NULL PRIMARY KEY REFERENCES dbo.Shop(ShopID),
  CurrentComputerRepairs SMALLINT NOT NULL DEFAULT (0),
  RepairEstimatedHours TINYINT NOT NULL DEFAULT (0),
  ModifiedDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()
);

CREATE TABLE dbo.ShopRetail
(
  ShopID INT NOT NULL PRIMARY KEY REFERENCES dbo.Shop(ShopID),
  NextPromotionDate DATETIME NULL,
  CurrentWeekSalesEstimate MONEY NULL,
  ModifiedDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()
);

To simplify SELECT operations, you can create Views against each type, if you like:

CREATE VIEW dbo.FoodShop AS
SELECT sh.ShopID, sh.ShopTypeID, sf.MostPopularItem
FROM   dbo.Shop sh
INNER JOIN dbo.ShopFood sf
        ON sf.ShopID = sh.ShopID;

CREATE VIEW dbo.RepairShop AS
SELECT sh.ShopID, sh.ShopTypeID, sr.CurrentComputerRepairs, sr.RepairEstimatedHours
FROM   dbo.Shop sh
INNER JOIN dbo.ShopRepair sr
        ON sr.ShopID = sh.ShopID;

CREATE VIEW dbo.RetailShop AS
SELECT sh.ShopID, sh.ShopTypeID, sr.NextPromotionDate, sr.CurrentWeekSalesEstimate
FROM   dbo.Shop sh
INNER JOIN dbo.ShopRetail sr
        ON sr.ShopID = sh.ShopID;

EDIT:
I forgot to add in the "audit" fields of [CreateDate] and [ModifiedDate]. I have now added them to the tables above. Please note that:

• these fields are not in the [ShopType] table as there really is no purpose here, but that is a bit subjective and some people still like to know at least when the records were created, so maybe [CreateDate] is fine there

• the [CreateDate] field is not in the subclass tables as the assumption is that those rows are created at the same time as the parent record. But when it comes to updates, those might just touch one of the two tables, depending on which fields are being updated. If using SQL Server, it is possible to create the records for both tables in a single statement even, using the OUTPUT clause as follows:

  CREATE PROCEDURE dbo.Shop_CreateRepairShop
  (
    @ShopName NVARCHAR(100),
    @CurrentComputerRepairs SMALLINT,
    @RepairEstimatedHours TINYINT,
    @ShopID INT OUTPUT = -1
  )
  AS
  SET NOCOUNT ON;
  
  INSERT INTO dbo.Shop (ShopName, ShopTypeID)
    OUTPUT INSERTED.ShopID, @CurrentComputerRepairs, @RepairEstimatedHours
    INTO   dbo.ShopRepair (ShopID, CurrentComputerRepairs, RepairEstimatedHours)
    VALUES (@ShopName, 2); -- assuming ShopTypeID for "Repair Shop" = 2
  
  SET @ShopID = SCOPE_IDENTITY();