Organizing a MySQL Database

Question

I'm developing an application that will require me to create my first large-scale MySQL database. I'm currently having a difficult time wrapping my mind around the best way to organize it. Can anyone recommend any reading materials that show different ways to organize a MySQL database for different purposes?

I don't want to try getting into the details of what I imagine the database's main components will be because I'm not confident that I can express it clearly enough to be helpful at this point. That's why I'm just looking for some general resources on MySQL database organization.

Answer 1

The way I learned to work these things out is to stop and make associations.

In more object oriented languages (I'm assuming you're using PHP?) that force OO, you learn to think OO very quickly, which is sort of what you're after here.

My workflow is like this:

1. Work out what data you need to store. (Customer name etc.)
2. Work out the main objects you're working with (e.g. Customer, Order, Salesperson etc), and assign each of these a key (e.g. Customer ID).
3. Work out which data connects to which objects. (Customer name belongs to a customer)
4. Work out how the main objects connect to each other (Salesperson sold order to Customer)

Once you have these, you have a good object model of what you're after. The next step is to look at the connections. For example:

• Each customer has only one name.
• Each product can be sold multiple times to anybody
• Each order has only one salesperson and one customer.

Once you've worked that out, you want to try something called normalization, which is the art of getting this collection of data into a list of tables, still minimizing redundancy. (The idea is, a one-to-one (customer name) is stored in the table with the customer ID, many to one, one to many and many to many are stored in separate tables with certain rules)

That's pretty much the gist of it, if you ask for it, I'll scan an example sheet from my workflow for you.

Answer 2

Maybe I can provide some advices based on my own experience

• unless very specific usage (like fulltext index), use the InnoDB tables engine (transactions, row locking etc...)
• specify the default encoding - utf8 is usually a good choice
• fine tune the server parameters (*key_buffer* etc... a lot of material on the Net)
• draw your DB scheme by hand, discuss it with colleagues and programmers
• define data types based not only on the programs usage, but also on the join queries (faster if types are equal)
• create indexes based on the expected necessary queries, also to be discussed with programmers
• plan a backup solution (based on DB replication, or scripts etc...)
• user management and access, grant only the necessary access rights, and create a read-only user to be used by most of queries, that do not need write access
• define the server scale, disks (raid?), memory, CPU

Here are also some tips to use and create a database.