-- 1.
login_master
create table login(
login_id int(10) auto_increment primary key,
username varchar(50) not null unique,
email_id varchar(50) not null unique,
password varchar(64) not null,
log_date datetime default current_timestamp not null
);
-- 2. state_master
create table state(
state_id int(10) auto_increment primary key,
state_name varchar(50) not null
);
-- 3. city_master
create table city(
city_id int(10) auto_increment primary key,
city_name varchar(50) not null,
state_id int(10),
foreign key(state_id) references state(state_id)
);
-- 4. area_master
create table area_master(
area_id int(10) auto_increment primary key,
address varchar(299) not null,
city_id int(10),
foreign key(city_id) references city(city_id)
);
-- 5. user_master
create table user(
user_id int(10) auto_increment primary key,
f_name varchar(50) not null,
l_name varchar(50) not null,
gender varchar(6) not null,
email_id varchar(50) not null,
password varchar(32) not null,
mobile_no bigint(10) not null,
area_id int(10),
pincode int(6) not null,
profile_image varchar(200) not null,
foreign key(area_id) references area_master(area_id)
);
-- 6. other_service_provider_master
create table other_service_provider(
osp_id int(10) auto_increment primary key,
user_id int(10),
type_of_service varchar(100) not null,
shop_name varchar(50),
area_id int(10),
mobile_no bigint(10) not null,
pincode int(6) not null,
foreign key(user_id) references user(user_id),
foreign key(area_id) references area_master(area_id)
);
-- 7. feedback_and_review_master
create table feedback_and_review(
review_id int(10) auto_increment primary key,
username varchar(50) not null,
review varchar(299) not null,
user_id int(10),
foreign key(user_id) references user(user_id)
);
-- 8. seller_master
create table seller(
seller_id int(10) auto_increment primary key,
property_id int(10),
seller_name varchar(50) not null,
mobile_no bigint(10) not null,
user_id int(10),
foreign key(user_id) references user(user_id)
);
-- 9. property_master
create table property(
property_id int(10) auto_increment primary key,
property_type varchar(50) not null,
bhk_type int(10) not null,
property_area double not null,
furnishing_status varchar(20) not null,
seller_id int(10) not null,
area_id int(10) not null,
price int(10) not null,
floor_no int(10) not null,
property_images varchar(299) not null,
foreign key(area_id) references area_master(area_id),
foreign key(seller_id) references seller(seller_id)
);
-- *. altering seller table for foreign key of property table after its creation...
ALTER TABLE seller
ADD CONSTRAINT fk_property
FOREIGN KEY (property_id)
REFERENCES property(property_id);
-- 10. buyer_master
create table buyer(
buyer_id int(10) auto_increment primary key,
property_id int(10),
mobile_no bigint(10) not null,
buyer_name varchar(50) not null,
user_id int(10),
foreign key(property_id) references property(property_id),
foreign key(user_id) references user(user_id)
);
-- 11. resident_master
create table resident(
resident_id int(10) auto_increment primary key,
property_id int(10),
property_type varchar(50) not null,
foreign key(property_id) references property(property_id)
);
-- 12. commercial_master
create table commercial(
commercial_id int(10) auto_increment primary key,
property_id int(10),
property_type varchar(50) not null,
foreign key(property_id) references property(property_id)
);
-- 13. agreement_master
create table agreement(
ag_id int(10) auto_increment primary key,
ag_status bool not null,
buyer_id int(10),
property_id int(10),
seller_id int(10),
ag_start_date datetime default current_timestamp not null,
ag_end_date datetime not null,
foreign key(buyer_id) references buyer(buyer_id),
foreign key(property_id) references property(property_id),
foreign key(seller_id) references seller(seller_id)
);
-- 14. document_master
create table document(
doc_id int(10) auto_increment primary key,
document_type varchar(50) not null,
file_name varchar(50) not null,
buyer_id int(10),
seller_id int(10),
upload_date datetime default current_timestamp not null,
document_image varchar(299) not null,
foreign key(buyer_id) references buyer(buyer_id),
foreign key(seller_id) references seller(seller_id)
);
-- 15. payment_master
create table payment(
payment_id int(10) auto_increment primary key,
payment_date datetime default current_timestamp not null,
buyer_id int(10),
seller_id int(10),
payment_method varchar(50) not null,
transaction_type varchar(50) not null,
property_id int(10),
amount int(10) not null,
transaction_status varchar(50) not null,
notes text,
ag_id int(10),
foreign key(buyer_id) references buyer(buyer_id),
foreign key(seller_id) references seller(seller_id),
foreign key(property_id) references property(property_id),
foreign key(ag_id) references agreement(ag_id)
);
