MongoDB – Type of Applications
Good fit
heavy read
Bad fit
Ad hoc queries
Updates
Joins
Social Applications
Social applications can go from 0 users to a million users in a very short time. The best DB fit for these kinds of applications is the DB that can easily scale horizontally which is where MongoDB comes into the picture.
Online Advertisement
Ads can be saved and updated from all kinds of sources. Data is not really related to each other but there is a lot of them.
Data Archive
Archiving a lot of data that may or may not be retrieved often can be done with a NoSQL DB like MongoDB.
MongoDB – Why nosql or mongodb?
Performance
MongoDB tends to be faster than relational DB when it comes to retrieving data. NoSQL database scales horizontally and is designed to scale to hundreds of millions and even billions of users doing updates as well as reads
Relational DB tends to be faster on ad hoc queries, updates, and joins. But applications can be designed and developed in a way to avoid these use cases. For example: to update you can use primary keys to update.
Differences
NoSQL scales horizontally (add more servers) but SQL Relational DB scales vertically (add more memories).
SQL relational DB must know columns and data types of the data to store. NoSQL DB does not have to know columns and data types. A row in a SQL relational DB contains all columns even if they are empty. A row in a NoSQL DB may or may not have all columns.
Advantages
Big data.
Schema changes are problematic and time-consuming in the world of SQL. NoSQL is great when business requirements are not stable and keep changing. You don’t have to modify or alter table and column definitions which is a painful process.
Scalability.
Disadvantages
MongoDB – Data Modeling
July 30, 2019SQL DATE_FORMAT
DATE_FORMAT(date, format)
| %a | Abbreviated weekday name (Sun to Sat) |
| %b | Abbreviated month name (Jan to Dec) |
| %c | Numeric month name (0 to 12) |
| %D | Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, …) |
| %d | Day of the month as a numeric value (01 to 31) |
| %e | Day of the month as a numeric value (0 to 31) |
| %f | Microseconds (000000 to 999999) |
| %H | Hour (00 to 23) |
| %h | Hour (00 to 12) |
| %I | Hour (00 to 12) |
| %i | Minutes (00 to 59) |
| %j | Day of the year (001 to 366) |
| %k | Hour (0 to 23) |
| %l | Hour (1 to 12) |
| %M | Month name in full (January to December) |
| %m | Month name as a numeric value (00 to 12) |
| %p | AM or PM |
| %r | Time in 12 hour AM or PM format (hh:mm:ss AM/PM) |
| %S | Seconds (00 to 59) |
| %s | Seconds (00 to 59) |
| %T | Time in 24 hour format (hh:mm:ss) |
| %U | Week where Sunday is the first day of the week (00 to 53) |
| %u | Week where Monday is the first day of the week (00 to 53) |
| %V | Week where Sunday is the first day of the week (01 to 53). Used with %X |
| %v | Week where Monday is the first day of the week (01 to 53). Used with %X |
| %W | Weekday name in full (Sunday to Saturday) |
| %w | Day of the week where Sunday=0 and Saturday=6 |
| %X | Year for the week where Sunday is the first day of the week. Used with %V |
| %x | Year for the week where Monday is the first day of the week. Used with %V |
| %Y | Year as a numeric, 4-digit value |
| %y | Year as a numeric, 2-digit value |
Examples
| %Y-%m-%d | 7/4/2013 |
| %e/%c/%Y | 4/7/2013 |
| %c/%e/%Y | 7/4/2013 |
| %d/%m/%Y | 4/7/2013 |
| %m/%d/%Y | 7/4/2013 |
| %e/%c/%Y %H:%i | 4/7/2013 11:20 |
| %c/%e/%Y %H:%i | 7/4/2013 11:20 |
| %d/%m/%Y %H:%i | 4/7/2013 11:20 |
| %m/%d/%Y %H:%i | 7/4/2013 11:20 |
| %e/%c/%Y %T | 4/7/2013 11:20 |
| %c/%e/%Y %T | 7/4/2013 11:20 |
| %d/%m/%Y %T | 4/7/2013 11:20 |
| %m/%d/%Y %T | 7/4/2013 11:20 |
| %a %D %b %Y | Thu 4th Jul 2013 |
| %a %D %b %Y %H:%i | Thu 4th Jul 2013 11:20 |
| %a %D %b %Y %T | Thu 4th Jul 2013 11:20:05 |
| %a %b %e %Y | Thu Jul 4 2013 |
| %a %b %e %Y %H:%i | Thu Jul 4 2013 11:20 |
| %a %b %e %Y %T | Thu Jul 4 2013 11:20:05 |
| %W %D %M %Y | Thursday 4th July 2013 |
| %W %D %M %Y %H:%i | Thursday 4th July 2013 11:20 |
| %W %D %M %Y %T | Thursday 4th July 2013 11:20:05 |
| %l:%i %p %b %e, %Y | 7/4/2013 11:20 |
| %M %e, %Y | 4-Jul-13 |
| %a, %d %b %Y %T | Thu, 04 Jul 2013 11:20:05 |
MySQL Date Types
INTEGER
INT can be sign or unsign.
The ZEROFILL attribute can be used to add zero padding.
CREATE TABLE user(
id INT AUTO_INCREMENT PRIMARY KEY,
salary INT(7) ZEROFILL,
days_off INT(3) ZEROFILL
);
INSERT INTO mysql_playground.user (salary,days_off) VALUES (150,4);
AUTO_INCREMENT is used with INT but only with positive integers. MySQL is not supporting negative int with auto_increment at the time of writing.
DECIMAL
Decimal is used for numbers with decimal.
column_name DECIMAL(precision, decimals); column_name DECIMAL(precision,0) is the same as column_name DECIMAL(precision); column_name DECIMAL; // default precision is 10
Example: for DECIMAL(20,5), 5 for decimal part and 15 for the integer part.
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
total_cost DECIMAL(19 , 4 ) ZEROFILL
);
INSERT INTO `orders` (`total_cost`) VALUES ('120.43');
The above example shows the precision as well as the decimal. Decimal is very useful in accounting numbers.
BOOLEAN
Currently, boolean is not supported by mysql. MySQL is using TINYINT as in 0 for false and 1 for true.
CREATE TABLE members (
id INT PRIMARY KEY AUTO_INCREMENT,
deleted BOOLEAN
);
INSERT INTO `members` (`deleted`) VALUES (true); INSERT INTO `members` (`deleted`) VALUES (false);
Since boolean is stored as TINYINT, numbers like 2,3, or 4 can be stored like 1 and 0. true is 1 and false is 0. if you store any other int like 2, for example, it won’t pick up in the query.
CHAR
CHAR is a character type that stores 0-255 characters only. MySQL adds padding with spaces to values to the length you declare. MySQL will only store values up to the length declared and ignore the rest.
CREATE TABLE covers (
id INT PRIMARY KEY AUTO_INCREMENT,
status CHAR(3)
);
INSERT INTO `covers` (`status`) VALUES ('NO');
INSERT INTO `covers` (`status`) VALUES ('YES');
INSERT INTO `covers` (`status`) VALUES (' Y ');
INSERT INTO `covers` (`status`) VALUES ('TEST');
From the example above, you can see that ‘ Y ‘ is trimmed the trailing space. You can also see that TEST is cut off to TES because of the length declared.
MySQL trims trailing spaces when they retrieve char values. MySQL does not consider trailing spaces when it does a comparison(<,>,=) between chars.
VARCHAR
VARCHAR is a character type that stores up to 65,535 characters. if a column is less than 255 bytes the length of the prefix is 1 byte else if a column is greater than 255 bytes the length of the prefix is 2 bytes.
The maximum row size is 65,535 bytes.
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(3)
);
INSERT INTO `mysql_playground`.`products` (`title`) VALUES ('WASHER');
Depending on your version of mysql, if you insert/update a value of varchar to be greater than the length declared, it might cut off the value, which happens in this case, or throw an error.
VARCHAR, unlike CHAR, does not add padding with spaces. It also does not remove trailing spaces when it retrieves or compares values.
TEXT
TEXT is a character type that stores long text string from 1 byte to 4 GB. TEXT is not stored in server memory but in disk which is much slower.
TEXT has four types:
TINYTEXT – 1 Byte (255 characters),
TEXT – 64KB (65,535 characters),
MEDIUMTEXT – 16MB (16,777,215 characters),
LONGTEXT – 4GB (4,294,967,295 characters)
DATE
DATE default format is yyyy-mm-dd but of course you can change the default format using DATE_FORMAT(date, format)
To define a date column: created_at DATE.
MySQL DATE values range from 1001-01-01 to 9999-12-31. If you want other values from this range then you can use the int data type.
TIME
TIME default format is HH:MM:SS
To define a time column: created_at TIME.
DATETIME
DATETIME default format is YYYY-MM-DD HH:MM:SS. Datetime does not keep timezone.
TIMESTAMP
TIMESTAMP default is YYYY-MM-DD HH:MM:SS. TIMESTAMP is saved in the UTC timezone. When you store a TIMESTAMP, the mysql server will take that TIMESTAMP and convert it from the connection timezone to UTC timezone and then save in db. When retrieving, MySQL converts TIMESTAMP from UTC to the connection timezone.