House-Rental-Managemnet-System-API/db.sql at master · AjithRock/House-Rental-Managemnet-System-API · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
USE home;

CREATE TABLE  tblProperty (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Address VARCHAR(20),
    Country varchar(20),
    City VARCHAR(20),
    State VARCHAR(20),
    Zip VARCHAR(20),
    Deleted bit NOT NULL DEFAULT 0,
	CreationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	CreatedBy INT NUll,
	UpdatedDate	TIMESTAMP NULL,
	UpdatedBy INT NULL,
	DeletedDate TIMESTAMP NULL,
	DeletedBy INT NULL
)  ENGINE=INNODB;


CREATE TABLE IF NOT EXISTS `customers` (
  id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  email varchar(255) NOT NULL,
  name varchar(255) NOT NULL,
  active BOOLEAN DEFAULT false
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE  luUnitType (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Deleted bit NOT NULL DEFAULT 0,
	CreationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	CreatedBy INT NUll,
	UpdatedDate	TIMESTAMP NULL,
	UpdatedBy INT NULL,
	DeletedDate TIMESTAMP NULL,
	DeletedBy INT NULL
)  ENGINE=INNODB;


INSERT INTO luUnitType (Name) VALUES ('Bike');
INSERT INTO luUnitType (Name) VALUES ('Car');
INSERT INTO luUnitType (Name) VALUES ('Shop');
INSERT INTO luUnitType (Name) VALUES ('House');


CREATE TABLE  tblUnit (
    UnitID INT AUTO_INCREMENT PRIMARY KEY,
    UnitName VARCHAR(30) NOT NULL,
    UnitTypeID Int NOt NULL,
    FOREIGN KEY (UnitTypeID) REFERENCES luUnitType(ID),
    PropertyID Int NOt NULL,
    FOREIGN KEY (PropertyID) REFERENCES tblProperty(ID),
    AreaInSqft VARCHAR(20),
    Description VARCHAR(100),
    Deleted bit NOT NULL DEFAULT 0,
	CreationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	CreatedBy INT NUll,
	UpdatedDate	TIMESTAMP NULL,
	UpdatedBy INT NULL,
	DeletedDate TIMESTAMP NULL,
	DeletedBy INT NULL
)  ENGINE=INNODB;


CREATE TABLE home.tblTenant (
    TenantID INT AUTO_INCREMENT,
    TenantName VARCHAR(50) NOT NULL,
    Age int null,
    GenderID Int NOt NULL,
    FOREIGN KEY (GenderID) REFERENCES refgender(GenderID),
    OccupationID Int NOt NULL,
    FOREIGN KEY (OccupationID) REFERENCES refoccupation(OccupationID),
    DateOfBirth date null,
    ContactNumber VARCHAR(20),
    Email VARCHAR(100),
    Deleted bit NOT NULL DEFAULT 0,
	CreationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	CreatedBy INT NUll,
	UpdatedDate	TIMESTAMP NULL,
	UpdatedBy INT NULL,
	DeletedDate TIMESTAMP NULL,
	DeletedBy INT null,
	CONSTRAINT tblTenant_pk PRIMARY KEY (TenantID)
)  ENGINE=INNODB;


CREATE TABLE home.tblPayment (
    PaymenID INT AUTO_INCREMENT,
    BillingID INT NOT NULL,
    TenantID INT NOT NULL,
    UnitID INT NOT NULL,
    PaymentType INT NOT NULL,
    PaymentDate	TIMESTAMP NOT NULL,
    Deleted bit NOT NULL DEFAULT 0,
	CreationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	CreatedBy INT NUll,
	UpdatedDate	TIMESTAMP NULL,
	UpdatedBy INT NULL,
	DeletedDate TIMESTAMP NULL,
	DeletedBy INT null,
	CONSTRAINT tblPayment_pk PRIMARY KEY (PaymenID)
)  ENGINE=INNODB;