Query for creating database for a new project

I hope that if my question is out of context you guys will let me pass by.I am not good at all when it comes to DBMS and all i can till in my 7+ years of IT experience is able to write some basic queries (with help from Google).

I am working on my own project and i am all done with the design phase and very will clear what all table structure i need to have for the project.I am going to use MySQL for this and since project is with the capabilities of small CMS in itself which means a lot of data to 开发者_如何转开发be handled at the database size.

I am using TOAD for MySQL to create schema and other things for MySQL but while doing this i have few queries which i want to address in the beginning.

1. What should be the storage engine type for MySQL (default is InnoDB)
2. Any other parameters needs to taken care of storage Engine
3. what Character set and Collation need to be set at table level and for column level if any (Application will have to take care of internationalization )

being not much knowledgeable about the database aspect these are the question keep popping in my mind and any help in this regard will be appreciated.

---

You should stick with InnoDB. It has transactions and a row-level locking. For high (writing) concurrency the row-level locking allows multiple INSERTs/UPDATEs/DELETEs at the same time. MyIsam has table-locking, meaning only one modifying query at a time. If you're read-heavy, MyISAM may perform better though. SELECT COUNT(*) FROM table; for example is a statistical thing MyISAM can answer instantly, while InnoDB actually does a COUNT(). InnoDB lets you define connections between your tables (Foreign Key Constraints) to help you with your data integrity.

If you're interested in things like the above, I recommend reading High Performance MySQL from O'Reilly Media.

Regarding the Character Set you should make sure that all tables / columns use the same character set. Preferably Unicode (UTF8). Make sure your mysql client is also set up to use UTF8, to avoid (transparent) transliteration of your data. (In fact, make sure the whole app does UTF8 from storage to display).

The Collation is something that lets you define your text being UTF8, but in a, say, German, context. With that knowledge, MySQL will properly sort "fööbar" and "foobar" according to the rules of the German language. (sorting amongst other things). I usually use "utf8_general_ci". Have a look at some examples over at mysql: http://dev.mysql.com/doc/refman/5.0/en/charset-collation-effect.html

---

Probably you will get better answer, but quickly from my experiences:

1. InnoDB is good for creating reliable data-driven web application and MyISAM is good for performance. But InnoDB supports Foreign Keys, transactions and row-level-locking which makes you honest while designing tables. It means if you don't want to create a simple and experimental database, the solution is using InnoDB.
2. You should look at Foreign Keys, transactions and Row-level locking (Only InnoDB supports these features)
3. utf8_general_ci is the most popular one I know if you want to storage different languages' data. It won't let you down.

I hope this help.