Showing posts with label MySQL. Show all posts
Showing posts with label MySQL. Show all posts

Friday, October 30, 2015

Symfony Classifieds Bundle - define requirements and database structure

 As settled in the previous post I will be defining the business requirements and based on those  create the database structure. Below is the current list of features I want to have in my app:

- 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;

Monday, October 26, 2015

database first vs code first and InnoDB vs MyISAM

I started a new project from scratch, creating a Classifieds Ads  bundle for Symfony 2. It will be available on Github at this link:  https://github.com/cristianpana86/CPANAClassifiedsBundle

"chicken or the egg" => "database first vs code first"

With all the efforts done to decouple applications from storage (database) you could say that I should write code first.  That is correct, just that I want to see how easy is to write a Symfony bundle starting from an existing database, like when you have an old application and you want to migrate it to Symfony.


I extracted from stackoverflow two advices on the process of building an application:

1.  Begin by writing down every function of your site. Define which objects you'll need and sketch the relationships between those objects. Design your database only when you have this information ready. It makes it a lot easier to see which tables  are needed (generally one table per object type) and which relationship fields you'll need.


2. Your logical data model should effectively capture the business requirements of your application. Your physical database design should be based on the logical data model combined with the necessary changes that you as a DBA feel are needed to maximize the efficiencies of your RDBMS.

If you are finding that you have to make numerous changes to the underlying database design through out the software development life cycle of your application it is indicative of two things:

    - Scope creep - You're allowing new requirements to be introduced at an inappropriate time.
   -  Insufficient Business Requirements - Your data modeler(s) (or system analysts) did not sufficiently translate the requirements from the business analysts. This resulted in an incomplete or incorrect data model to support the requirements of your application.

That being said once an application has been turned over to production it is not uncommon to have to go back and make iterative changes to the data model to support the natural evolution of the application or underlying business processes.

OK, so I will first define the business requirements and after this is done design the Entity-Relationship Diagram.  Another question arises, should I use foreign keys? InnoDB vs MyISAM?

Another piece of wisdom from stackoverflow users about foreign keys:
"A database schema without FK constraints is like driving without a seat belt."
"Foreign keys help enforce referential integrity at the data level. They also improve performance because they're normally indexed by default.  can't imagine designing a database without foreign keys. Without them, eventually you are bound to make a mistake and corrupt the integrity of your data. They are not required, strictly speaking, but the benefits are huge. "
Below an argumentation on using InnoDB taken from here:
http://stackoverflow.com/questions/7492771/should-i-use-myisam-or-innodb-tables-for-my-mysql-database?rq=1

"Always use InnoDB by default.

In modern versions of MySQL, that is 5.1 and 5.5, you should use InnoDB. In MySQL 5.1, you should enable the InnoDB plugin. In MySQL 5.5, the InnoDB plugin is enabled by default so just use it.
The advice years ago was that MyISAM was faster in many scenarios. But that is no longer true if you use a current version of MySQL.
There may be some exotic corner cases where MyISAM performs marginally better for certain workloads (e.g. table-scans, or high-volume INSERT-only work), but the default choice should be InnoDB unless you can prove you have a case that MyISAM does better.
Advantages of InnoDB besides the support for transactions and foreign keys that is usually mentioned include:
  • InnoDB is more resistant to table corruption than MyISAM.
  • Row-level locking. In MyISAM, readers block writers and vice-versa.
  • Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
  • MyISAM is stagnant; all future development will be in InnoDB.
Virtually the only reason to use MyISAM in current versions is for FULLTEXT indexes. And that's due to be supported in InnoDB in MySQL 5.6 (update: indeed InnoDB supports FULLTEXT in 5.6, but it's pretty buggy still, as of 5.6.13)."

Wednesday, July 8, 2015

PHP basic pagination with MySQL

First step: create new branch in GitHub from the GitHub. Code. Download the code, from the GitHub application on local machines. Modify the code, commit changes to this branch and synchronize with GitHub server.  When I finished with the development I make a Pull Request to merge the branch with the repository.

I will display a certain number of posts per page (now is hard coded to 2), and 2 buttons (links) "Older posts" and "Newer Posts".

I am using MySQL "LIMIT" clause to specify the number of records to be returned. From http://www.w3schools.com/php/php_mysql_select_limit.asp
     
     The SQL query below says "return only 10 records, start on record 16 (OFFSET 15)":
        $sql = "SELECT * FROM Orders LIMIT 10 OFFSET 15";

It seems that the default type for bindParam is string so you will get an error if you do not specify to be an integer:   PDO:PARAM_INT:
    http://stackoverflow.com/questions/4544051/sqlstate42000-syntax-error-or-access-violation-1064-you-have-an-error-in-you
       
        $sth=$this->db->prepare("SELECT * FROM blogposts LIMIT :per_page OFFSET :page_number ;");
        $sth->bindParam(':per_page', $per_page, PDO::PARAM_INT);
        $sth->bindParam(':page_number', $page_numb, PDO::PARAM_INT);
        $sth->execute();



-added pagination.xml file in \model to store current page number and number of posts per page (this should be changed in the future as having one current page for all the users accessing the website is not right.)  Later edit: problem solved in this post http://phpbeginnertoadvanced.blogspot.co.uk/2015/07/fix-pagination.html

-added routes for /blog/older and /blog/newer 

You will have access to the code files when releasing the v0.4