Chapter 9 - Making Tables in MySQL
With this structure in mind, it is useful now to start putting this into MySQL. ALthough as I mentioned above, I used MySQL Workbench to create the diagrams themselves, it can be rather awkward and cumbersome to use, so we will create the databases within phpMyAdmin.
Select the wishlist database that you created in Chapter 7; MySQL will show "No tables found in database". Give the new table a name ("item"), and tell it how many columns it will have ("5" - you can easily change this later), then press "Go".
Fill out the table, with the field definitions that we came up with earlier. INT means Integer, i.e., a whole number, and VARCHAR means a CHARacter field of VARiable length. Here we'll give title and url a maximum length of 255 characters, which should be enough. In practice URLs could be longer than that in real life (though this is rare).
VARCHAR Length
Prior to MySQL version 5.0.3, a VARCHAR can be between 0-255 characters long; from 5.0.3 and on, it can be up to 65,535 characters long.
The "Null" column of checkmarks tells the database whether or not it is valid for the field to be null. Here, I've said that the user-entered values could be null, if the user really wanted them to be, but structurally, it wouldn't make sense to not have an item_id, and arguably, it might not make sense to have an item which does not belong to a wishlist. You may disagree on this point, and find it valid to have a floating "pool" of unassigned items, but whatever you choose as your policy, the database will enforce it, and will not allow a record to be added with a null column if the structure of the database tells it that a null value is not allowed. This can help to maintain consistency of the database, even if the application erroneously tries to insert an item without a wishlist_id. Again, this is Defense in Depth, using the database structure to prevent bugs in the application from causing database inconsistencies.
For the id field, the "Index" column being set to "PRIMARY" tells MySQL that id is the primary index, though we also want to index the data by the price column, so that it can efficiently sort items by price when required. This can make it easy for a user to choose how the list is ordered. The price field therefore gets a regular (not primary) "INDEX" marker.
The "A_I" column stands for "Auto Increment" - this means that whenever we insert an item into the table, it will automatically be given the next available integer as its id column. So when you add the fifth item into the database, it will be given an id of 5, without you having to worry about what id to use, and whether or not it is unique.
Repeat this activity for the wishlist table. Again, id is the Primary Index, and has the Auto Increment flag set. Without Auto Increment, the id field would have to be set manually by the application, with all sorts of concurrency issues (e.g., what happens when two new lists are added at the same time?), which are best dealt with within the database server code than reimplemented by every application ever to use a database.
Do the same again for the user table. We will be revisiting the password details later; this is just a placeholder for the real password solution. This can not be stated strongly enough: whatever the application, however important or insignificant it may be, passwords must be stored securely, and there is more to this than first meets the eye.
