Design an SQL schema for a banking system with accounts and transactions tables. What constraints and indexes are necessary?

For a banking system with accounts and transactions tables, the schema can be designed as follows:

Accounts Table:

• account_id (PK, unique account identifier)
• user_id (foreign key to user, if available)
• balance (current balance, decimal)
• currency (account currency)
• created_at (creation date)

Transactions Table:

• transaction_id (PK)
• account_id (FK to accounts.account_id)
• amount (transaction amount, decimal, positive or negative)
• transaction_type (e.g., "deposit", "withdrawal", "transfer")
• timestamp (transaction time)
• description (optional)

Constraints:

• In accounts, balance should not be negative (if business logic requires it).
• In transactions, account_id must exist in accounts (FOREIGN KEY).
• amount in transactions should not be zero.

Indexes:

• PRIMARY KEY on account_id and transaction_id.
• Index on transactions.account_id for quick transaction lookups by account.
• Index on transactions.timestamp for sorting and time-based queries.

This design ensures data integrity and efficient access to account and transaction information.