slappbooks-database-schema

sql

CREATE TABLE entity (
	id INT AUTO_INCREMENT,
	name VARCHAR(255),
	currency VARCHAR(10),
	type VARCHAR(255),
	PRIMARY KEY (id)
);

CREATE TABLE transaction (
	id INT AUTO_INCREMENT,
	transaction_id VARCHAR(255) UNIQUE,
	set_id VARCHAR(255),
	date DATE,
	entity_id INT NOT NULL,
	cheque_no VARCHAR(50),
	voucher_no VARCHAR(50),
	is_credit INT,
	amount DOUBLE,
	notes VARCHAR(255),
	reconcile INT,
	PRIMARY KEY (id),
	FOREIGN KEY (entity_id) REFERENCES entity (id) ON DELETE CASCADE
);

CREATE TABLE conversion (
	id INT AUTO_INCREMENT,
	transaction_id VARCHAR(255) NOT NULL,
	to_currency VARCHAR(10),
	from_currency VARCHAR(10),
	rate DOUBLE,
	PRIMARY KEY (id),
	FOREIGN KEY (transaction_id) REFERENCES transaction (transaction_id) ON DELETE CASCADE
);

INSERT INTO entity (id, name, currency, type) VALUES (1, "Cash", "LKR", "ASSETS");
INSERT INTO entity (id, name, currency, type) VALUES (2, "Bank", "LKR", "ASSETS");
INSERT INTO entity (id, name, currency, type) VALUES (3, "Sales", "LKR", "INCOME");