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