1
\$\begingroup\$

What I'm trying to achieve is to create a site for a real estate agency, which has the responsibility to sell or rent someone else's property. I'm not sure how far from correct what I've created is. I used id int auto_increment as the primary key for all tables since I've read that it's more efficient this way. And since 3NF is a requirement if you want your database to be efficient I've tried to achieve that but I'm not sure if i've done it correctly. If you can spot any mistake or have a proposal I would really appreciate it.

Assumption

  • 1 user can own many properties, 1 property can be owned by 1 user

    1 to N relation so the primary key of users will become a foreign key in properties.

  • 1 property can be located in 1 city_area, in 1 city_area many properties can be located

    1 to N relation so the primary key of city_area will become a foreign key in properties.

  • 1 property can be rented by 1 user, 1 user can rent many properties

    (I guess it’s possible for a person to be renting 2 or more places at the same time, but a property can be rented by only 1 person)

    1 to N relation so the primary key of city_area will become a foreign key in properties.

Tables

users
(user_id, last_name, first_name, email, mobile_phone, password, 
authority, creation_date)

Candidate keys:  (user_id), (email), (mobile_phone)
Primary key:(user_id)

properties
(property_id, reference, rent_or_sell_or_both, plot_area, cov_area, bedrooms, bathrooms, 
description, user_id(fk), city_area_id(fk), creation_date)

Candidate keys:  (property_id), (reference)
Primary key:(property_id)

city_area(Areas with the same name can exist in different cities) 
(city_area_id, city_name, area_name)

Candidate keys:  (city_area_id), (city_name, area_name)
Primary key: (city_area_id)

property_rented_by(to store who is paying rent to stay in a property)
(property_id(fk), user_id(fk))

Candidate keys:  (property_id, user_id)
Primary key: (property_id, user_id)

checked_by_ip  (I need it in order to count how many different ips 
have checked a property)
(ip, property_id(fk), creation_date)

Candidate keys:  (ip, property_id(fk))
Primary key: (ip, property_id(fk))
  1. It is 1NF because there are no multivalued or composite attributes
  2. it is 2NF because there is no dependency to a part of a composite key
  3. It is not 3NF because users and Properties are not in 3NF

In order for Users to be in 3NF we will break users to 3 tables:

users
(user_id, last_name, first_name, password, authority, creation_date)

Candidate keys: (users_id), (email) 
Primary key: user_id

Users_email
(users_id(fk)),email)

Candidate keys: (users_id), (email)
Primary key: (users_id)

Users_mobile_phone
(users_id(fk)), mobile_phone)

Candidate keys: (users_id), (mobile_phone)
Primary key: (users_id)

In order for Properties to be in 3NF we will break properties to 2 tables:

properties
(property_id, rent_or_sell_or_both, plot_area, cov_area, bedrooms, bathrooms, 
description, user_id(fk), city_area_id(fk), creation_date)

Candidate keys: (property_id)
Primary key: (property_id)

Properties_reference
(property_id(fk), reference)

Candidate keys: (property_id), (reference)
Primary key: (property_id)

Final MySQL code for creating the tables (correct order)

create table city_area(
city_area_id int not null auto_increment,
area varchar(32) not null,
city varchar(32) not null,
primary key(city_area_id)
);

create table users(
user_id int not null auto_increment,    
last_name varchar(24) not null,
first_name varchar(24) not null,
password binary(64) not null,
authority smallint,
creation_date DATETIME DEFAULT CURRENT_TIMESTAMP,
primary key (user_id)
);

create table users_email(
user_id  int not null,
email varchar(32) not null,
primary key (user_id),
foreign key (user_id) references users(user_id) on delete cascade
);

create table users_mobile_phone(
user_id  int not null,
mobile_phone varchar(16) not null,
primary key (user_id),
foreign key (user_id) references users(user_id) on delete cascade
);

create table properties(
property_id int not null auto_increment,
rent_or_sell_or_both tinyint not null,
plot_area float,
cov_area float,
bedrooms tinyint not null,
bathrooms tinyint not null,
description varchar(255),
user_id int,
city_area_id int not null,
creation_date DATETIME DEFAULT CURRENT_TIMESTAMP,
primary key (property_id),
foreign key (user_id) references users(user_id),
foreign key (city_area_id) references city_area(city_area_id)
);

create table properties_reference(
property_id int not null,
reference varchar(16) not null,
primary key (property_id),
foreign key (property_id) references properties(property_id) on delete cascade
);

create table property_rented_by(
property_id int not null,
user_id int not null,
creation_date DATETIME DEFAULT CURRENT_TIMESTAMP,
primary key (property_id, user_id),
foreign key (property_id) references properties(property_id) on delete cascade,
foreign key (user_id) references users(user_id) on delete cascade
);

create table checked_by_ip(
property_id int not null,
ip varchar(32) not null,
creation_date DATETIME DEFAULT CURRENT_TIMESTAMP,
primary key (ip, property_id),
foreign key (property_id) references properties(property_id) on delete cascade
);
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.