Before you even start designing the database, it would be extremely beneficial to read up on some MySQL literature and view some examples of quality code (there are plenty just have to google). Just some basic pointers though:
- Choose the right engine; the most most popular two for MySQL are InnoDB and MyISAM. If you plan on having a highly trafficked site with possibly hundreds of writes/reads per second, then you would benefit greatly from InnoDB's row level locking. If you need it to be transaction safe then you NEED InnoDB as MyISAM is not ACID compliant. If transaction are not an issue and you have say greater than 80-90% reads compared to writes, then you'd probably be fine with MyISAM (the reason you want low writes for MyISAM is it uses table locking for all reads, which will prevent any actions on the table from taking place until the current query finishes). From my experience the difference on both isn't noticeable.
- Use the proper data types for each column; if you need to store a username that will be a maximum of 16 characters long, do not make it a TEXT column, instead make it a VARCHAR(16) column.
- You must learn how to create and use indexes properly unless you want a slow site. In order for MySQL to be extremely fast, it must be able to take advantage of indexes on columns. Learn about indexes and put them only on your most used columns - do NOT go overboard as they take up a lot of space!
- Make sure that if you use any JOIN statements, they are on indexed columns.
- Test things out yourself. While you can learn a lot from reading and viewing examples, some of the more valuable lessons will be taught through your own trial and error.
Those are the main things I can think of right now but hopefully it helps you out a little bit.
How do I plan my php mysql database?
Linear Mode
