· 8 years ago · Feb 21, 2018, 02:52 PM
1-- phpMyAdmin SQL Dump
2-- version 4.4.15.10
3-- https://www.phpmyadmin.net
4--
5-- Host: localhost
6-- Generation Time: Feb 21, 2018 at 02:29 PM
7-- Server version: 5.7.20
8-- PHP Version: 5.4.16
9
10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11SET time_zone = "+00:00";
12
13
14/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
15/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
16/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
17/*!40101 SET NAMES utf8mb4 */;
18
19--
20-- Database: `deposit_guard`
21--
22
23-- --------------------------------------------------------
24
25--
26-- Table structure for table `properties`
27--
28
29CREATE TABLE IF NOT EXISTS `properties` (
30 `property_id` int(11) NOT NULL,
31 `landlord_id` int(11) NOT NULL,
32 `prop_address` varchar(255) NOT NULL,
33 `county` varchar(11) NOT NULL,
34 `prop_description` text,
35 `no_of_tenants` int(2) NOT NULL
36) ENGINE=InnoDB DEFAULT CHARSET=latin1;
37
38-- --------------------------------------------------------
39
40--
41-- Table structure for table `rtrn`
42--
43
44CREATE TABLE IF NOT EXISTS `rtrn` (
45 `property_id` int(4) NOT NULL,
46 `tenant_id` int(4) NOT NULL,
47 `count` int(1) NOT NULL DEFAULT '0',
48 `rtrn_amnt` int(4) NOT NULL,
49 `message` varchar(500) NOT NULL,
50 `rtrn_id` smallint(5) unsigned NOT NULL DEFAULT '0'
51) ENGINE=InnoDB DEFAULT CHARSET=latin1;
52
53-- --------------------------------------------------------
54
55--
56-- Table structure for table `rtrn_images`
57--
58
59CREATE TABLE IF NOT EXISTS `rtrn_images` (
60 `id` int(11) NOT NULL,
61 `image_url` varchar(1000) NOT NULL,
62 `rtrn_id` smallint(5) unsigned NOT NULL DEFAULT '0'
63) ENGINE=InnoDB DEFAULT CHARSET=latin1;
64
65-- --------------------------------------------------------
66
67--
68-- Table structure for table `tenants`
69--
70
71CREATE TABLE IF NOT EXISTS `tenants` (
72 `tenant_id` int(11) NOT NULL,
73 `listing_id` int(4) NOT NULL,
74 `name` varchar(255) NOT NULL,
75 `email` varchar(255) NOT NULL,
76 `deposit_amount` int(11) NOT NULL,
77 `paid` tinyint(1) DEFAULT '0',
78 `s_date` date DEFAULT NULL,
79 `duration` varchar(2) DEFAULT NULL,
80 `e_date` date DEFAULT NULL,
81 `initialized` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
82 `payment_date` date DEFAULT NULL,
83 `returned` tinyint(1) NOT NULL DEFAULT '0'
84) ENGINE=InnoDB DEFAULT CHARSET=latin1;
85
86-- --------------------------------------------------------
87
88--
89-- Table structure for table `users`
90--
91
92CREATE TABLE IF NOT EXISTS `users` (
93 `user_id` int(11) NOT NULL,
94 `username` varchar(255) NOT NULL,
95 `email` varchar(255) NOT NULL,
96 `password` varchar(255) NOT NULL,
97 `user_type` varchar(100) NOT NULL
98) ENGINE=InnoDB DEFAULT CHARSET=latin1;
99
100--
101-- Indexes for dumped tables
102--
103
104--
105-- Indexes for table `properties`
106--
107ALTER TABLE `properties`
108 ADD PRIMARY KEY (`property_id`),
109 ADD UNIQUE KEY `listing_id` (`property_id`) USING BTREE,
110 ADD KEY `landlord_id` (`landlord_id`);
111
112--
113-- Indexes for table `rtrn`
114--
115ALTER TABLE `rtrn`
116 ADD PRIMARY KEY (`rtrn_id`),
117 ADD KEY `property_id` (`property_id`),
118 ADD KEY `tenant_id` (`tenant_id`);
119
120--
121-- Indexes for table `rtrn_images`
122--
123ALTER TABLE `rtrn_images`
124 ADD PRIMARY KEY (`id`),
125 ADD KEY `rtrn_id` (`rtrn_id`);
126
127--
128-- Indexes for table `tenants`
129--
130ALTER TABLE `tenants`
131 ADD PRIMARY KEY (`tenant_id`),
132 ADD UNIQUE KEY `tenant_id` (`tenant_id`),
133 ADD KEY `listing_id` (`listing_id`);
134
135--
136-- Indexes for table `users`
137--
138ALTER TABLE `users`
139 ADD PRIMARY KEY (`user_id`);
140
141--
142-- AUTO_INCREMENT for dumped tables
143--
144
145--
146-- AUTO_INCREMENT for table `properties`
147--
148ALTER TABLE `properties`
149 MODIFY `property_id` int(11) NOT NULL AUTO_INCREMENT;
150--
151-- AUTO_INCREMENT for table `rtrn_images`
152--
153ALTER TABLE `rtrn_images`
154 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
155--
156-- AUTO_INCREMENT for table `tenants`
157--
158ALTER TABLE `tenants`
159 MODIFY `tenant_id` int(11) NOT NULL AUTO_INCREMENT;
160--
161-- AUTO_INCREMENT for table `users`
162--
163ALTER TABLE `users`
164 MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;
165--
166-- Constraints for dumped tables
167--
168
169--
170-- Constraints for table `properties`
171--
172ALTER TABLE `properties`
173 ADD CONSTRAINT `properties_to_users` FOREIGN KEY (`landlord_id`) REFERENCES `users` (`user_id`);
174
175--
176-- Constraints for table `rtrn`
177--
178ALTER TABLE `rtrn`
179 ADD CONSTRAINT `rtrn_to_properties` FOREIGN KEY (`property_id`) REFERENCES `properties` (`property_id`),
180 ADD CONSTRAINT `rtrn_to_tenants` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`tenant_id`);
181
182--
183-- Constraints for table `rtrn_images`
184--
185ALTER TABLE `rtrn_images`
186 ADD CONSTRAINT `images_to-_rtrn` FOREIGN KEY (`rtrn_id`) REFERENCES `rtrn` (`rtrn_id`);
187
188--
189-- Constraints for table `tenants`
190--
191ALTER TABLE `tenants`
192 ADD CONSTRAINT `tenants_to_properties` FOREIGN KEY (`listing_id`) REFERENCES `properties` (`property_id`);
193
194/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
195/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
196/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;