Create a gist now

Instantly share code, notes, and snippets.

@malithj /sql
Created Feb 7, 2018

What would you like to do?
slappbooks-database-schema
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");
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment