Updated to have more-correct terminology and an 'is_debit' column:
I am designing a (PostgreSQL) schema for a lottery website that uses double-entry accounting.
'jackpots', 'users', and 'house' are each tables which reference an account_id, which in turn is used in the journal to record a credit or debit of money 'amount'.
I want to assure that the account_id is only in one table -- and that it is unique in that table.
What is the best way to refactor the tables so that account_id is guaranteed to appear only 1x in all of them?
What I've considered:
Creating another table, 'account_type' that has different codes for user accounts, jackpot accounts, and the house's account. Then add two colums to 'accounts', ('account_type' and 'ref_id', the latter of which will reference the id of 'jackpots', 'users', or 'house'). This seems somewhat inelegant since I wouldn't know how to link the table type to the table name using SQL, alone. I don't mind using triggers, though, if this is the only way for an elegant, foolproof solution.
Looking for some sort of built in constraint that says account_id cannot be used more than once among 3 tables. Would guess that this doesn't exist, however.
Help is appreciated!