Financial Database Table Creation Tutorial

Posted on Apr 13, 2010

This is the SQL command set that can be uploaded to the database and automatically create and fill the tables.

Download the file from from HERE.

To view the SQL and copy/paste it, you can see that below.

[code lang="SQL"]
CREATE TABLE masterlogin (
    ml_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username varchar(120) NOT NULL,
    password VARCHAR(30) NOT NULL
) TYPE = MYISAM;

INSERT INTO masterlogin (ml_id, username, password)
VALUES (null, 'tuliplogin', 'adminpass');

CREATE TABLE maincategory (
    mc_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    maincat varchar(120) NOT NULL
) TYPE = MYISAM;

INSERT INTO maincategory (mc_id, maincat)
VALUES (null, 'INCOME');

INSERT INTO maincategory (mc_id, maincat)
VALUES (null, 'SAVINGS');

INSERT INTO maincategory (mc_id, maincat)
VALUES (null, 'HOUSING');

INSERT INTO maincategory (mc_id, maincat)
VALUES (null, 'UTILITIES');

INSERT INTO maincategory (mc_id, maincat)
VALUES (null, 'FOOD');

INSERT INTO maincategory (mc_id, maincat)
VALUES (null, 'TRANSPORTATION');

INSERT INTO maincategory (mc_id, maincat)
VALUES (null, 'CLOTHING');

INSERT INTO maincategory (mc_id, maincat)
VALUES (null, 'MEDICAL / HEALTH');

INSERT INTO maincategory (mc_id, maincat)
VALUES (null, 'PERSONAL');

INSERT INTO maincategory (mc_id, maincat)
VALUES (null, 'RECREATION');

INSERT INTO maincategory (mc_id, maincat)
VALUES (null, 'DEBTS');

INSERT INTO maincategory (mc_id, maincat)
VALUES (null, 'OUTSTANDING CHECKS');


CREATE TABLE subcategory (
    sc_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    subcat varchar(120) NOT NULL
) TYPE = MYISAM;

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Paycheck');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Cash / Checks');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Transfer from Savings');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Transfer to savings');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Emergency Fund');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Mortgage');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Repairs');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Maintenence');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Electricity');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Water');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Gas');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Phone');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Cable');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Internet');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Grocery');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Dining Out');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Car Payment');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Insurance');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'License and Taxes');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Adults');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Children');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Disability Insurance');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Doctor');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Personal Trainer');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Gym Membership');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Medicine');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Pets');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Education');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Life Insurance');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Subscriptions');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Gifts');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'School Supplies / Books');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Netflix');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Entertainment');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Vacation');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Vacation Savings');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Visa');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Medical Bill');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Bank Visa');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Student Loan');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Home Equity Loan');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Dept Store Card');

INSERT INTO subcategory (sc_id, subcat)
VALUES (null, 'Gasoline');



CREATE TABLE catrelationship (
    cr_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    mainid INT(11) NOT NULL,
    subid INT(11) NOT NULL
) TYPE = MYISAM;

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 1, 1);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 1, 2);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 1, 3);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 2, 4);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 2, 5);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 3, 6);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 3, 7);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 3, 8);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 4, 9);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 4, 10);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 4, 11);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 4, 12);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 4, 13);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 4, 14);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 5, 15);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 5, 16);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 6, 17);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 6, 8);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 6, 43);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 6, 7);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 6, 18);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 6, 19);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 7, 20);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 7, 21);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 8, 22);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 8, 23);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 8, 24);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 8, 25);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 8, 26);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 8, 27);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 9, 28);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 9, 29);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 9, 30);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 9, 31);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 9, 32);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 10, 33);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 10, 34);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 10, 35);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 10, 36);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 11, 37);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 11, 38);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 11, 39);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 11, 40);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 11, 41);

INSERT INTO catrelationship (cr_id, mainid, subid)
VALUES (null, 11, 42);

CREATE TABLE register (
    rg_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    transdate timestamp(14) NOT NULL,
    transtype VARCHAR(30) NOT NULL,
    checknum INT (11),
    payee VARCHAR (120) NOT NULL,
    debit FLOAT(18,2) NOT NULL default 0.0,    
    credit FLOAT(18,2) NOT NULL default 0.0,
    memo VARCHAR (120),
    crid INT (11) NOT NULL   
) TYPE = MYISAM;

INSERT INTO register (rg_id, transdate, transtype, checknum, payee, debit, credit, memo, crid)
VALUES (null, '2010-03-01', 'DDA ACH WITHDRAWAL', null,'KY BANK MORTGAGE PAYMENTS',-1500, 0, null,6);

INSERT INTO register (rg_id, transdate, transtype, checknum, payee, debit, credit, memo, crid)
VALUES (null, '2010-03-03', 'DDA ACH WITHDRAWAL', null,'BANKCARD SERVICE BILL PAYMT',-100, 0, null,44);

INSERT INTO register (rg_id, transdate, transtype, checknum, payee, debit, credit, memo, crid)
VALUES (null, '2010-03-04', 'DDA ACH WITHDRAWAL', null,'KENTUCKY AMERICA BILL PAYMT',-30, 0, null,10);

INSERT INTO register (rg_id, transdate, transtype, checknum, payee, debit, credit, memo, crid)
VALUES (null, '2010-03-05', 'DDA ACH DEPOSIT', null,'LEXMARK PAYMENTS', 0,2000, null,1);

INSERT INTO register (rg_id, transdate, transtype, checknum, payee, debit, credit, memo, crid)
VALUES (null, '2010-03-06', 'DDA ACH WITHDRAWAL', null,'STATE FARM RO 08 SFPP',-75, 0, null,10);

INSERT INTO register (rg_id, transdate, transtype, checknum, payee, debit, credit, memo, crid)
VALUES (null, '2010-03-10', 'DDA ACH WITHDRAWAL', null,'BANK OF AMERICA BILL PAYMT',-75, 0, null,40);

INSERT INTO register (rg_id, transdate, transtype, checknum, payee, debit, credit, memo, crid)
VALUES (null, '2010-03-12', 'DDA ACH WITHDRAWAL', null,'INSIGHT COMMUNIC BILL PAYMT',-65, 0, null,14);

INSERT INTO register (rg_id, transdate, transtype, checknum, payee, debit, credit, memo, crid)
VALUES (null, '2010-03-14', 'POS-PIN', null,'AT&T QD TNK EFT POS',-90, 0, null,12);

INSERT INTO register (rg_id, transdate, transtype, checknum, payee, debit, credit, memo, crid)
VALUES (null, '2010-03-17', 'DDA ACH WITHDRAWAL', null,'WEST COAST LIFE INS. PREM.',-40, 0, null,32);


CREATE TABLE budget (
    bu_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    budgetdate timestamp(14) NOT NULL,
    debit FLOAT(18,2) NOT NULL default 0.0,    
    credit FLOAT(18,2) NOT NULL default 0.0,
    freq VARCHAR (120),
    crid INT (11) NOT NULL   
) TYPE = MYISAM;

INSERT INTO budget (bu_id, budgetdate, debit, credit, freq, crid)
VALUES (null, '2010-03-01', -1500, 0,'monthly',6);

INSERT INTO budget (bu_id, budgetdate, debit, credit, freq, crid)
VALUES (null, '2010-03-15', -450, 0,'monthly',17);

INSERT INTO budget (bu_id, budgetdate, debit, credit, freq, crid)
VALUES (null, '2010-03-06', 0, 2000,'biweekly',1);

INSERT INTO budget (bu_id, budgetdate, debit, credit, freq, crid)
VALUES (null, '2010-03-01', -75, 0,'monthly',10);

CREATE TABLE schedtran (
    st_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    scheddate timestamp(14) NOT NULL,
    payee VARCHAR (120) NOT NULL,
    debit FLOAT(18,2) NOT NULL default 0.0,    
    credit FLOAT(18,2) NOT NULL default 0.0,
    freq VARCHAR (120),
    crid INT (11) NOT NULL  
) TYPE = MYISAM;

INSERT INTO schedtran (st_id, scheddate, payee, debit, credit, freq, crid)
VALUES (null, '2010-03-01', 'Kentucky Bank Mortgage',-1500, 0,'monthly',6);
[/code]