Table specification is done, as you are probably aware, through the create table command. Usually you will want your scripts to contain specification of fields, options, indexes and foreign keys all in one place. Check online for syntactical guides. Personally, I usually handle this process through an ide. Many people prefer to start with a db design tool such as DBVisualizer. In this case, you create a picture of your database design with tables connected at foreign keys by lines. Then, when satisfied you reverse engineer a create script through the software. I am not a big fan of this method as it seems slow to me however for others who are more visual it seems to work well. My MySQL client tool of choice is Navicat. It is fairly easy to use relative to the other options out there. To create a table, I will fill out a form with Navicat’s create table option which in turn generates the create sql for me. There are many free options out there as well, however Navicat essentials offers ssh tunneling, the ability to directly access a database that is not directly accessible to the internet, and this is important enough to me to warrant the 40$ a Navicat essentials license costs.
In the past, specification of foreign keys has been a requirement of good database design. Many db’s feel the same way about this now, especially those that have been around for a while. For most MySQL implementations, i agree that this is a helpful way of ensuring data integrity and wise to use. In the case of really large data sets, however, it is important to understand the cost of using foreign keys. In many cases, it may be better to rely upon the application for this integrity. For example, when an insert happens on a table with a foreign key, MySQL has to go check the key in a recursive fashion to make sure all required rows exist in the database according to the key. While this is usually more efficient than handling this check yourself(what a mess), this check can be costly performance wise. A happy medium can be achieved by creating foreign keys to control small inserts coming from the application but then disabling them using foreign_key_checks=0 when doing bulk loads. Of course you as the administrator are much less likely to make mistakes than the application programmers (haha). The performance gain by disabling these checks in a rigorously keyed database can be huge. We will discuss more about this when we address database loading and ETL.