Antonio Conte Posted August 21, 2011 Share Posted August 21, 2011 1. Always name your tables the same way: There are best practice rules for naming tables. They should always be lowercase, split by underscore ( _ ) and named in plural. If you need to build tables for several purposes, (forums, shops, fruits) prefix them so the appear next to each other. Examples: forums_users forums_posts shops_products shops_prices shops_availability 2. Use normalization rules(!!!) when creating a structure for tables Larry explains the different forms of normalization very good in this book. Read it thoroughly, UNDERSTAND it, and plan your tables well. The rules are really not that hard to understand, and will allow you to cross-reference tables in an easy way later on. It will make you understand how the data you are using are working. It will make your systems a lot easier to build on and to introduce new features. I promise you, THIS is how you become a wizard when it comes to working with data in several tables. 3. Use white space(!) and use UPPERCASE for mysql functions (See tip #4!) When your queries becomes increasingly more complex, you should really follow these tips. To demonstrate, I will give you two versions of the exact same code: CREATE VIEW view_goals_per_game AS SELECT league.season AS season, league.id AS league_id, league.name AS league_name, goals.match_id AS match_id, clubs1.name AS hometeam, clubs2.name AS awayteam, players.id AS player_id, CONCAT( players.fornavn, ' ', players.etternavn ) AS player_name, goals.goal_time AS goal_time, games.kickoff_time AS kickoff_time FROM abc_players_goals AS goals INNER JOIN cnk_soccer_games AS games ON ( goals.match_id = games.id ) INNER JOIN cnk_soccer_clubs AS clubs1 ON ( games.club1_id = clubs1.id ) INNER JOIN cnk_soccer_clubs AS clubs2 ON ( games.club2_id = clubs2.id ) INNER JOIN abc_players AS players ON ( goals.player_id = players.id ) INNER JOIN cnk_soccer_league AS league ON ( league.id = games.league_id ) LIMIT 0, 1000 create view view_goals_per_game as select league.season as season, league.id as league_id, league.name as league_name, goals.match_id as match_id, clubs1.name as hometeam, clubs2.name as awayteam, players.id as player_id, concat( players.fornavn, ' ', players.etternavn ) as player_name, goals.goal_time, games.kickoff_time from abc_players_goals as goals inner join cnk_soccer_games as games on ( goals.match_id = games.id ) inner join cnk_soccer_clubs as clubs1 on ( games.club1_id = clubs1.id ) inner join cnk_soccer_clubs as clubs2 on ( games.club2_id = clubs2.id ) inner join abc_players as players on ( goals.player_id = players.id ) inner join cnk_soccer_league as league on ( league.id = games.league_id ) limit 0 , 1000 Whick one would you like to maintain? 4: Save your queries in a text editor Yes! It sound idiotic, right? It's not. Think of this happening: You accidentally delete, modify or overwrite a query you've used a lot of time on. It will save you a lot of time, tears and the life of a few keyboards! This is also leading up tip number 5. 5. Use views instead of customizing your dataset in PHP(!) This is a real life-saver. Think of it as including ONE central PHP file instead on chancing 10 documents every time you make a change. The views should be written to display AS MUCH information as possible. Try to think of every scenario you may want to use the data. Views are, with a few exceptions, for displaying data; hence it's name. NOTE: After the creation of a view, you CANNOT modify it. This is why you should follow step 4. - Views are really easy to create: [u][i]CREATE VIEW the_name_of_the_view AS[/i][/u] SELECT * FROM table1 The query used in tip #3 is a view. It would allow you to sort by a specific league, match_id, player_id or by kickoff_time. The view is used to display statistic about the goal scorer in a football match. (soccer for americans) The table for saving goal statistic has three rows(!). Match_id, player_id and goal_time. This is good data normalization, and minimizes redundancy and make for consistent data. That means the table players_goals need to be linked with other tables that holds data about the specific match and the specific player. This is the reason for create a view. Don't see the point of this? There really is one - I promise! By writing a view that is general and display a lot of data, I can write SIMPLE queries to get different results: 1. Last five goal scorers with name of both teams, player name and goal time SELECT hometeam, awayteam, player_name, goal_time FROM view_goals_per_game ORDER BY kickoff_time DESC 2. Display players with most goals in descending order SELECT player_name, COUNT(*) as goals FROM view_goals_per_game GROUP BY player_id ORDER BY goals DESC 3. Display all goals by a specific player SELECT hometeam, awayteam, player_name, goal_time FROM view_goals_per_game WHERE player_id = 10 ORDER BY kickoff_time DESC 4. Find all goals in a specific match SELECT hometeam, awayteam, player_name, goal_time FROM view_goals_per_game WHERE match_id = 837 Does it start to make some sense? Instead of repeating and modifying the same hard-to-grasp code over and over again (see tip #3), use a view to make your life simpler. Hope this has helped someone. I have been thinking about writing a lot of different tips when it comes to MySQL. I've been thinking about writing a guide to joins for example. I really don't know if this interests someone or not. Hoping others will also share tips and experiences when it comes to MySQL. 4 Link to comment Share on other sites More sharing options...
HartleySan Posted August 21, 2011 Share Posted August 21, 2011 This is a really awesome post, and deserves to be stickied, if that's possible on these forums. This is a lot of sage-like advice from a very knowledgeable guy that has obviously learned through experience. I especially like the bit about views. Too true! Even with the most simple sites, it really is amazing how taking the time to properly plans things at the beginning really pays off. Thanks for the post, Antonio. I also agree with saving all your queries in a text file. This is especially true for when using stored procedures/prepared statements. Anyway, thanks for this post, and I hope everyone reads it! Link to comment Share on other sites More sharing options...
Jonathon Posted August 21, 2011 Share Posted August 21, 2011 A little green +1 for you sir Link to comment Share on other sites More sharing options...
Larry Posted August 22, 2011 Share Posted August 22, 2011 Hello Antonio, Thanks for sharing your excellent thoughts. 1. Always name your tables the same way: There are best practice rules for naming tables. They should always be lowercase, split by underscore ( _ ) and named in plural. If you need to build tables for several purposes, (forums, shops, fruits) prefix them so the appear next to each other. I agree that coming up with a consistent naming scheme is vital, and is true for the columns as well. I generally split by underscore, but when I do OOP, I don't. In non-OOP code I do some_var; in OOP I would do someVar. If the database is going to be interacted with from an OOP perspective, then naming the tables and columns consistent with OOP is better (in my opinion). 3. Use white space(!) and use UPPERCASE for mysql functions (See tip #4!) I prefer to use uppercase for all SQL keywords, too. Just be careful not to put any white space between a MySQL function name and its opening parentheses. 4: Save your queries in a text editor Very good tip! 5. Use views instead of customizing your dataset in PHP(!) Another very good tip. VIEWS have helped me out of a lot of jams. In fact, I'm working on a project now where normalization created four tables that are hard to JOIN and the solution was not to de-normalize but to use a VIEW. Thanks again! Link to comment Share on other sites More sharing options...
HartleySan Posted August 23, 2011 Share Posted August 23, 2011 A question about views, since I've never used them: Do they reside on the database side just like stored procedures? I ask, because I'm wondering if they create a persistent virtual table that can be quickly referenced, or if they have to re-perform the necessary joins everytime they are queried. Thanks. Link to comment Share on other sites More sharing options...
Larry Posted August 23, 2011 Share Posted August 23, 2011 Yes, exactly, VIEWS reside in the database and can be queried as if they were real tables. I believe MySQL will automatically update the VIEW when the data in the underlying query tables changes. I don't believe MySQL re-does the JOIN with every query of the VIEW, as that would defeat the purpose. Link to comment Share on other sites More sharing options...
HartleySan Posted August 23, 2011 Share Posted August 23, 2011 That would defeat the purpose, wouldn't it? Anyway, very awesome. The next time I actually have the need for massive joins, I will look into views. Thank you. Link to comment Share on other sites More sharing options...
Antonio Conte Posted August 23, 2011 Author Share Posted August 23, 2011 Thanks for feedback, corrections and additions, Larry. Views does also update automatically. I can confirm that. Anyone have something to share? Link to comment Share on other sites More sharing options...
HartleySan Posted August 23, 2011 Share Posted August 23, 2011 Stored procedures/prepared statements are awesome! Link to comment Share on other sites More sharing options...
Antonio Conte Posted August 24, 2011 Author Share Posted August 24, 2011 When should you use them? I've read how Larry uses stored procedures to calculate distances to shops. But when should you really use them? What needs should you have to take them to use? I started this thread to share tips and tricks here. Anyone else got something good to share? Link to comment Share on other sites More sharing options...
Larry Posted August 24, 2011 Share Posted August 24, 2011 I specifically use stored functions in situations where very complex logic needs to be applied to column values, such as the mathematical calculation of distance on the earth based upon latitude and longitude. I use stored procedures for complex, multipart transactions and/or for improved security and/or for improved performance and/or for better separation of logic from presentation. I've written about this some in a newsletter: http://www.larryullman.com/2011/07/04/what-is-larry-thinking-42-doing-what-i-do-part-3/#qa Other things I would recommend would be to look into MySQL's storage engines and how it uses indexes. Understanding both goes a long way towards making the most of a database. Link to comment Share on other sites More sharing options...
Antonio Conte Posted August 24, 2011 Author Share Posted August 24, 2011 Stored functions seems awesome! Will check it out, as I have a query I REALLY need to improve on. The logic is not that hard in these calculation, but the query is not easily understandable. Btw: Congratulation on finishing your new book for print, Larry. Checked for the amazon widget in the article, but I couldn't find one. Adblock. Sorry. Link to comment Share on other sites More sharing options...
Larry Posted August 25, 2011 Share Posted August 25, 2011 Thanks, Antonio. I haven't updates the Amazon widget yet as the book isn't available yet, but will soon. Thanks for mentioning it! Link to comment Share on other sites More sharing options...
HartleySan Posted August 25, 2011 Share Posted August 25, 2011 Which book are we talking about? Also, I used stored procedures/prepared statements for all queries these days, as they're faster and more secure in all cases. Can't argue with that. Link to comment Share on other sites More sharing options...
sonal Posted November 14, 2011 Share Posted November 14, 2011 Trying to figure out a point about view: As far as I understood, I can write a complex query in a view, which can generate large amount of data. And then, I can write a smaller query on that view? Am I correct? Please let me know. Link to comment Share on other sites More sharing options...
Larry Posted November 14, 2011 Share Posted November 14, 2011 Yes, that's exactly correct. Link to comment Share on other sites More sharing options...
Max Posted November 14, 2011 Share Posted November 14, 2011 Antonio - regarding joins - go for it! This is a difficult concept to understand at first but once you start to get into them, the power is amazing, especially in a correctly normalized database. I also find the 'SQL' window in phpmyadmin very useful, where you can try out queries before including them into php texts. Link to comment Share on other sites More sharing options...
Josee Posted November 15, 2011 Share Posted November 15, 2011 I just wanted to say thanks to Antonio Conte for his explanations about views. I hadn't understood their real use until then, and now I couldn't do without them! @Max: have you tried views? When you have a query with lots of JOINs, a view is magic, because, once you've created it, you no longer have to refer to multiple tables but just to one. You just work hard once on your JOINs, and then you no longer have to think about them (except for saving the original query in a separate file, as Antonio Conte mentioned, just in case you have to recreate it one day). Link to comment Share on other sites More sharing options...
Max Posted November 15, 2011 Share Posted November 15, 2011 Hi Josee, No, views are new to me, but look very powerful. Isn't there a danger of clogging up the server - presumably the view has to be re-created (or updated) - see Larry's post 23 August 2011 - 5:39 AM - with a large amount of data every time there is an enquiry? Just one question - don't understand the bracketed u and i ..are they just some extraneous html??......... CREATE VIEW the_name_of_the_view AS SELECT * FROM table1 Link to comment Share on other sites More sharing options...
Josee Posted November 15, 2011 Share Posted November 15, 2011 Just one question - don't understand the bracketed u and i ..are they just some extraneous html??......... Yes; nothing to do with the view. As to clogging up the server, I'll let someone knowing more than I do answer you! Link to comment Share on other sites More sharing options...
Larry Posted November 16, 2011 Share Posted November 16, 2011 Yes, views put an extra load on the database server but it takes load off of the Web server. Which is better depends entirely upon the situation. Link to comment Share on other sites More sharing options...
markifornia Posted April 9, 2012 Share Posted April 9, 2012 [u][i]CREATE VIEW the_name_of_the_view AS[/i][/u] SELECT * FROM table1 I'm still on chapter 10 of the book. Are creating views addressed in later chapters? As easy it is to create as mentioned, I would like to learn about it in further detail. Ofcourse, finding it in this book is my first place of preference. Thanks, Mark Link to comment Share on other sites More sharing options...
Larry Posted April 9, 2012 Share Posted April 9, 2012 I don't think I discuss VIEWs in this book, although I could be wrong. I do discuss them in my MySQL book, I believe, and of course they're covered in the MySQL manual and elsewhere online. Link to comment Share on other sites More sharing options...
Recommended Posts