Building a cool website using HTML, PHP and MySQL

Further begining MySQL

If you remember, our database is Normalized. That is, each table has specific information that makes the data non repetitive and preserves its integrity. The Database has several tables. Here is a list.

mysql> show tables;
+---------------------+
| Tables_in_bookshelf |
+---------------------+
| authors             | 
| books               | 
| bookswritten        | 
| checkout            | 
| clients             | 
| topics              | 
+---------------------+
6 rows in set (0.00 sec)

Because there is a many to many relationship between books and authors, there is a seperate table, Bookswritten, that manages that relationship. The checkout table manages who has what book, and when it is due. The client table manages the clients, who have a one to many relationship with the books. One book can be checked out to only one client, (Unless we have multiple copies, and the assumption here is we don't. In the real world, there would be many copies, and there would be a copy-book table just like there is a bookswritten table) But each client can have several books, which is managed by the checkout table. It is also assumed that each book is shelved in one place, but that one place can have many books there. So the topic-books relationship is one to many.

Some tables have lots of data, like the books table. Because the table is so large, we won't look at the whole thing. Just pieces. But this is how it is organized.

The field gives the name of each column. The type gives the kind of data stored, and how much space is given the field. There aren't that many topics, so 999 topics is ample. I discovered that 37 charachters is too short for a title. The live version of this database has a 45 character limit.

Each field name that has a relation has to have the same type in each table with the relation. Book IDs are integers, date fields have date information and so on.

mysql> show columns in books;
+--------------+---------------------+------+-----+-------------------+----------------+
| Field        | Type                | Null | Key | Default           | Extra          |
+--------------+---------------------+------+-----+-------------------+----------------+
| book_id      | smallint(7)         | NO   | PRI | NULL              | auto_increment | 
| topic_id     | tinyint(3) unsigned | NO   |     | NULL              |                | 
| date_aquired | timestamp           | YES  |     | CURRENT_TIMESTAMP |                | 
| description  | tinytext            | NO   |     | NULL              |                | 
| title        | varchar(37)         | NO   |     | NULL              |                | 
+--------------+---------------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)

I don't yet have an employee table. We can use that to explain the process of creating tables next.

Resources

Building a cool website using HTML, PHP and MySQL

Further begining MySQL