- anyone can see the ads (no login required)
- only REGISTERED USER can post ads
- minimum information about user: EMAIL, PASSWORD, PHONE, NAME, LASTNAME
- an user after login can manage his ads: see ADS LIST, EDIT(includes reactivate EXPIRED AD), DELETE buttons
- an USER can can edit/modify/delete an AD after LOGIN
- an ad can be normal (free) or promoted. an ad is promoted for a period of time - after this time it becomes normal ad and it will expire as any ad.
- in the same page where you add/edit an ad you can enter the code of your payment or promotional code which makes the ad to be upgraded to PROMOTED
- doing the payment is not in the scope of this project, could be an extension
- the ADMIN USER creates CATEGORIES and subcategories, USERS can post ads only in existing categories.
- a CATEGORY can be one of the main categories (no parent) or it can have a PARENT CATEGORY, in this case it is a subcategory.
- an unlimited number of levels of subcategories can be added by ADMIN
- an AD can be posted in just ONE CATEGORY
- an AD cannot be posted directly on a CATEGORY/SUBCATEGORY which has at least a SUBCATEGORY
- an AD EXPIRES after a certain period of time (it will not be displayed but still exist in the database).
- an EXPIRED AD can be REACTIVATED
- limit the number of ADS an USER can post in one day ( 30 should be enough).
- an AD contains: TITLE, CATEGORY, USER NAME (owner), USERs contact method (email/phone), PRICE, DESCRIPTION,
TAGS, PHOTOS, STATUS (active/expired) DATE when posted, calculated expiration date (not displayed), SPECIFIC ITEMS per CATEGORY defined by ADMIN
- a CATEGORY can have SPECIFIC ITEMS (fields) which will be inherited in any child SUBCATEGORY
(example: CAR category has items like Color, it will be present in sub-categories like "New Cars" and "Used Cars")
- ADMIN sets maximum number of PHOTOS and maximum PHOTO size. Application should come with default values.
- SEARCH feature directly in all records without selecting information like category/subcategory
- search by LOCATION
- ORDER BY function, with parameters: PRICE LOW, PRICE HIGH, NEWEST FIRST
- detailed SEARCH: CATEGORY, SUBCATEGORY (is any), price minimum, price maximum,CURRENCY
- ADMIN sets a BASE CURRENCY and any other accepted CURRENCIES, ADMIN sets conversion rate between BASE CURRENCY and the others
- have a MOST RECENT ADS view
- AD LOCATION country/region/city/city_area
For drawing the data model diagram I am using www.draw.io It very easy to use, you can save your work directly on cloud or on your local computer.
I wrote the SQL for creating these tables in my MySQL installation :
Server version: 5.6.15-log - MySQL Community Server (GPL) )
#Category table, subcategories are categories with parent. Root categories have parent_id = NULL
CREATE TABLE category(
id_category INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(50) NOT NULL,
parent_id INT NULL,
CONSTRAINT fk_parent_id_category
FOREIGN KEY fk_parent_id (parent_id)
REFERENCES category(id_category)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
#SpecificItemsTypes table - specific items types are for example items type related to cars: year of production, no of kilometres, ABS,
#Or about an apartment: surface, year of construction, etc.
CREATE TABLE specific_items_types(
id_specific_item_type INT AUTO_INCREMENT PRIMARY KEY,
item_type_name VARCHAR(50)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
#SpecificItemsTypes_meta - meta table between "category" and "specific_items_types"
CREATE TABLE specific_items_types_meta(
id_specific_meta INT AUTO_INCREMENT PRIMARY KEY,
id_specific_item_type INT NOT NULL,
id_category INT NOT NULL,
CONSTRAINT fk_id_category
FOREIGN KEY fk_id_category (id_category)
REFERENCES category(id_category),
CONSTRAINT fk_id_specific_item_type
FOREIGN KEY fk_id_specific_item_type (id_specific_item_type)
REFERENCES specific_items_types(id_specific_item_type)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
#SpecificItems - holds the values for the actual items. for example specific item "surface" will have FK id 2 and value yes.
CREATE TABLE specific_items(
id_specific_item INT AUTO_INCREMENT PRIMARY KEY,
id_specific_item_type INT NOT NULL,
id_ads INT UNSIGNED NOT NULL,
item_value VARCHAR(50) NOT NULL,
CONSTRAINT fk_id_ads
FOREIGN KEY fk_id_ads (id_ads)
REFERENCES ads(id_ads),
CONSTRAINT fk_id_specific_item_types
FOREIGN KEY fk_id_specific_item_type (id_specific_item_type)
REFERENCES specific_items_types(id_specific_item_type)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
#Currencies table
CREATE TABLE currencies(
id_currency INT AUTO_INCREMENT PRIMARY KEY,
currency_name VARCHAR(30) NOT NULL,
base_currency TINYINT(1) NOT NULL,
convertion_rate FLOAT(10,2) NOT NULL,
enabled TINYINT(1) NOT NULL
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
#Ads
CREATE TABLE ads(
id_ads INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
id_user INT NOT NULL,
id_city_area INT NOT NULL,
id_category INT NOT NULL,
id_currency INT NOT NULL,
title VARCHAR(100) NOT NULL,
description VARCHAR(500) NOT NULL,
price BIGINT NOT NULL,
email VARCHAR(255) NOT NULL,
phone_number VARCHAR(20),
path_photos VARCHAR(1024),
created_at DATETIME NOT NULL,
expires_at DATETIME NOT NULL,
spam TINYINT(1) NOT NULL,
CONSTRAINT fk_id_user
FOREIGN KEY fk_id_user (id_user)
REFERENCES fos_user(id),
CONSTRAINT fk_id_city_area
FOREIGN KEY fk_id_city_area (id_city_area)
REFERENCES city_areas(id_city_area),
CONSTRAINT fk_id_currency
FOREIGN KEY fk_id_currency(id_currency)
REFERENCES currencies(id_currency) ,
CONSTRAINT fk_id_categories
FOREIGN KEY fk_id_categories(id_category)
REFERENCES category(id_category)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
#PromotedAsPayment - under construction!!
CREATE TABLE promoted_ads_payment(
id_promoted_ads_payment INT AUTO_INCREMENT PRIMARY KEY,
id_ads INT UNSIGNED NOT NULL,
payment_code VARCHAR(50) NOT NULL,
payment_date DATETIME NOT NULL,
expiration_date_promo DATETIME NOT NULL,
CONSTRAINT fk_id_ads_promo
FOREIGN KEY fk_id_ads (id_ads)
REFERENCES ads(id_ads)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
#Countries
CREATE TABLE countries(
id_country INT AUTO_INCREMENT PRIMARY KEY,
country_code VARCHAR(2) NOT NULL,
country_name VARCHAR(50) NOT NULL
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
#Regions
CREATE TABLE regions(
id_region INT AUTO_INCREMENT PRIMARY KEY,
id_country INT NOT NULL,
region_name VARCHAR(50) NOT NULL,
CONSTRAINT fk_id_country
FOREIGN KEY fk_id_country (id_country)
REFERENCES countries(id_country)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
#Cities
CREATE TABLE cities(
id_city INT AUTO_INCREMENT PRIMARY KEY,
id_region INT NOT NULL,
city_name VARCHAR(50) NOT NULL,
CONSTRAINT fk_id_region
FOREIGN KEY fk_id_region (id_region)
REFERENCES regions(id_region)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
#City Areas
CREATE TABLE city_areas(
id_city_area INT AUTO_INCREMENT PRIMARY KEY,
id_city INT NOT NULL,
city_area_name VARCHAR(50) NOT NULL,
CONSTRAINT fk_id_city
FOREIGN KEY fk_id_city (id_city)
REFERENCES cities(id_city)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
----------------------------
----------------------------
I also inserted some data to some tests for now, (I will create some data fixtures later).
Please see below sources from where I taken countries list, list of regions in France, list of cities in France (some editing is needed before putting those in my tables)
For the other fields(ads, currencies, catergories) I used my imagination
https://github.com/raramuridesign/mysql-country-list/blob/master/mysql-country-list.sql
https://github.com/pixelastic/sql-french-cities/blob/master/regions.sql
https://raw.githubusercontent.com/pixelastic/sql-french-cities/master/cities.sql
-------------------------------------
-------------------------------------
Some queries to test my data model:
#Select category name and specific items allocated to it
SELECT c.category_name
,i.item_type_name
from category as c
INNER JOIN specific_items_types_meta as meta
ON meta.id_category=c.id_category
INNER JOIN specific_items_types as i
ON meta.id_specific_item_type = i.id_specific_item_type
WHERE c.category_name="cars";
#SELECT a complete AD from all tables related (without specific items)
SELECT a.title
, a.description
, a.price
, c.currency_name
, a.phone_number
, ca.city_area_name
, a.expires_at
, u.name
FROM ads AS a
INNER JOIN currencies AS c
ON c.id_currency=a.id_currency
INNER JOIN fos_user AS u
ON a.id_user=u.id
INNER JOIN city_areas AS ca
ON a.id_city_area = ca.id_city_area
WHERE a.id_ads=1
----------------------------------------------------
#SELECT specific_items for an AD
SELECT a.id_ads
, a.title
, sit.item_type_name
, si.item_value
FROM specific_items AS si
INNER JOIN ads AS a
ON a.id_ads = si.id_ads
INNER JOIN specific_items_types AS sit
ON si.id_specific_item_type = sit.id_specific_item_type
WHERE a.id_ads=1;
