yrA0JVDy

1Create SCHEMA IF NOT EXISTS Accounting;
2use accounting;
3
4CREATE TABLE IF NOT EXISTS `Account` (
5  `ID` Int auto_increment primary key,
6  `Name` Varchar(30),
7  `Amount` Decimal(10,2),
8  `Balance` Decimal(10.2),
9  `BankNumber` VarChar(12)
10);
11
12CREATE TABLE IF NOT EXISTS `Revenue` (
13  `ID` Int auto_increment primary key,
14  `AccountID` Int,
15  `DateCreated` datetime,
16  `Amount` Decimal(10,2),
17  `Category` Varchar(100),
18  Foreign KEY (`AccountID`) References Account(ID)
19);
20
21CREATE TABLE IF NOT EXISTS `User` (
22  `ID` Int auto_increment primary key,
23  `Username` Varchar(40),
24  `Password` Varchar(400),
25  `Salt` Varchar(100),
26  `Permissions` Varchar(50)
27);
28
29CREATE TABLE IF NOT EXISTS `Expense` (
30  `ID` Int auto_increment primary key,
31  `AccountID` Int,
32  `DateCreated` datetime,
33  `Amount` Dec(10,2),
34  `Category` Varchar(100),
35  Foreign KEY (`AccountID`) References Account(ID)
36);
37
38CREATE TABLE IF NOT EXISTS `Petition` (
39  `ID` Int auto_increment primary key,
40  `AccountID` int,
41  `Amount` Dec(10,2),
42  `DateCreated` datetime,
43  `Status` Int,
44  `Category` Varchar(100),
45  Foreign KEY (`AccountID`) References Account(ID)
46);
47
48CREATE TABLE IF NOT EXISTS `TaxRate` (
49  `ID` Int auto_increment primary key,
50  `Zip` Int,
51  `Rate` Dec(4,2)
52);
53
54CREATE TABLE IF NOT EXISTS `Category` (
55  `ID` Int auto_increment primary key,
56  `CatName` Varchar(100)
57);
58
59insert into Account VALUES (null, 'Main', 10000000, 10000000, 111111);
60insert into Account VALUES (null, 'Sales', 1000000, 1000000, 111112);
61insert into Revenue VALUES (null, 1, now(), 5000, 'sale');
62insert into Revenue VALUES (null, 1, now(), 10000, 'sale');
63insert into Petition VALUES (null, 1, 10000, now(), 0, 'pizza party');
64insert into Expense VALUES (null, 1, now(), 1000, 'Hookers');
65insert into TaxRate VALUES (null, 16133, 0.06);