Several on here believe my disdain for ORMs stems from the fact I'm not knowledgeable and/or efficient. So if you're still learning development, here's some reasons why you should spend the time to learn proper SQL, and the various database functions available. I could be wrong, but if you're learning strictly ORMs, I think most of this stuff is going to be left out.
We'll start with the most simplistic:
1. Date Functions
One thing I don't see many times is the proper use of date functions. This is especially important if you're releasing a distributable software, as on many servers the system timezone is different from the database timezone. For example, say the next payment date needs to be pushed up a month:
Say you want to group a bunch of payments by year:
Instead of the above simple SQL statement, I see people literally running a loop and executing a separate SQL query for each year.
2. Cascading
Excellent if you have child tables that contain data on a parent table. For example, maybe you have a products table, and a list of attributes stores within the product_attributes table. When a product is deleted, I see many people executing two SQL statements to delete the product data from both tables. Instead, you can create your attributes table such as:
Or if the attributes table already exists, add the foreign key:
Now any time a product is deleted, all of its attributes data will be deleted as well. No reason to delete from both tables within your code.
3. ON DUPLICAT KEY UPDATE
Excellent for things such as aggregate data. For example, maybe you have a table named payments, and instead of heavily querying it everytime the admin wants to view a report, you store a monthly summary in the payments_summary table, which maybe looks like:
Every time a payment comes in, this table needs to be updated, so the admin can view reports in real-time. When updating it, you can use a simple SQL query such as:
That's it. Instead of checking to see if the relevant row already exists, then inserting or updating as needed, you just need that one SQL statement, and the database will take care of the rest.
4. Triggers
Taking the above example, we can move that entirely into the database using a trigger, such as:
Execute the above SQL code against the database once, and that's it. You don't have to type a single line of code into your software, and your monthly aggregate data of all payments will be automatically maintained within the payments_summary table.
I still haven't even touched on different things such as views, stored procedures, etc. Nonetheless, if you're not currently utilizing the above, you should be as it makes your code leaner, and is simply the proper way to do things. Lean on the database engine as much as you can, instead of relying on a ORM or your own code. That's what the database is there for.
EDIT: Typos in the above SQL code should be pretty obvious. Incapsula wouldn't let the post through without them.
We'll start with the most simplistic:
1. Date Functions
One thing I don't see many times is the proper use of date functions. This is especially important if you're releasing a distributable software, as on many servers the system timezone is different from the database timezone. For example, say the next payment date needs to be pushed up a month:
Code:
UPDATE subscriptions SET next_date = date(date_add(next_date, interval 1 month)) WHERE id = $id
Code:
SELECT YEAR(date_added) AS year, count(*) AS total, SUM(amount) AS amount FROM transaction GROUP BY YEAR(date_added)2. Cascading
Excellent if you have child tables that contain data on a parent table. For example, maybe you have a products table, and a list of attributes stores within the product_attributes table. When a product is deleted, I see many people executing two SQL statements to delete the product data from both tables. Instead, you can create your attributes table such as:
Code:
CREATE TABL product_attributes (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
product_id INT NOT NULL,
attr_name VARCHAR(50) NOT NULL,
attr_value VARCHAR(50) NOT NULL,
FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE
) engine=InnoDB;
Code:
ALTE TABLE product_attributes ADD FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE3. ON DUPLICAT KEY UPDATE
Excellent for things such as aggregate data. For example, maybe you have a table named payments, and instead of heavily querying it everytime the admin wants to view a report, you store a monthly summary in the payments_summary table, which maybe looks like:
Code:
CREAT TABLE payments_summary (
id VARCHAR(20) NOT NULL PRIMARY KEY,
year SMALLINT NOT NULL,
month SMALLINT NOT NULL,
method VARCHAR(30) NOT NULL,
payments INT NOT NULL,
amount DECIMAL(8,2) NOT NULL
) engine=InnoDB;
Code:
$guid = $year . '-' . $month . '-' . $payment_method;
INSER INTO payments_summary('$guid', $year, $month, '$payment_method', 43, 1594) ON DUPLICATE KEY UPDATE payments = payments + 43, amount = amount + 15944. Triggers
Taking the above example, we can move that entirely into the database using a trigger, such as:
Code:
DELIMITER @@
CREAT TRIGGER payments_after_insert AFTER INSERT ON payments FOR EACH ROW
BEGIN
DECLARE guid varchar(30);
SELECT CONCAT_WS('-', YEAR(NEW.date_added), MONTH(NEW.date_added), NEW.method) INTO guid;
INSER INTO payments_summary VALUES (guid, YEAR(NEW.date_added), MONTH(NEW.date_added), NEW.method, 1, NEW.amount) ON DUPLICATE KEY UPDATE payments = payments + 1, amount = amount + NEW.amount;
END;
@@
DELIMITER ;I still haven't even touched on different things such as views, stored procedures, etc. Nonetheless, if you're not currently utilizing the above, you should be as it makes your code leaner, and is simply the proper way to do things. Lean on the database engine as much as you can, instead of relying on a ORM or your own code. That's what the database is there for.
EDIT: Typos in the above SQL code should be pretty obvious. Incapsula wouldn't let the post through without them.
