Organizing a MySQL Database

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 differe开发者_高级运维nt 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.

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.

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.

I can recomend you the first chapter of this book: An Introduction to Database Systems, it may help you organize your ideas, and I certainly recomend not using 5th normal form but using 4th, this is very important.

If I could only give you one piece of advice, that would be to generate test data at similar volumes as production and benchmark the main queries.

Just make sure that the data distribution is realistic. (Not all people are named "John", and not all people have unique names. Not all people give their phone nr, and most people won't have 10 phone numbers either).

Also, make sure that the test data doesn't fit into RAM (unless you expect the production data volumes to do too).