MySQL hardware database

Everyone needs a database. Even as teenager I needed one at home for my Vinyl LP (Long Play) Records. 

So here is the one, very simple database. 

Let's first see the diagram that explains database tables, its columns and relationship between tables.  

The purpose of this article is like very quick and basic introduction to creating MySQL database. 
But even simple, this example database (for hardware and software) can still be used for small organization or say, non- profit agency. 
And it can be foundation for more complex database you can develop by yourself. 

The example is done with MySQL version shown below: 

/opt/webstack/mysql/5.1/bin> mysqladmin -u root -p version
Enter password:
mysqladmin  Ver 8.42 Distrib 5.1.30, for sun-solaris2.10 on sparc
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version          5.1.30-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 20 hours 45 min 19 sec
Threads: 2  Questions: 52  Slow queries: 0  Opens: 19  Flush tables: 1  Open tables: 12  Queries per second avg: 0.0


As you can see, it's fresh installation with default three databases. 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)


Let's create our database, named companyhw. 

mysql> CREATE DATABASE companyhw;
Query OK, 1 row affected (0.09 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| companyhw          |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.01 sec)


To start working with new database, you have to "switch" to it like below. As you can see there is no any table.  

mysql> use companyhw;
Database changed

mysql> show tables;
Empty set (0.00 sec)


Basically, tables are simple. 

1. There is the primary key, unique integer number (INT), cannot be negative (UNSIGNED) and null (NOT NULL), increments its value by 1 ( AUTO_INCREMENT).
2. Other data is just VARCHAR type ( string, I setup max 100 characters, longer ones will be trancuated).

mysql> CREATE TABLE office ( office_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, officename VARCHAR(100));
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+---------------------+
| Tables_in_companyhw |
+---------------------+
| office              |
+---------------------+
1 row in set (0.00 sec)

mysql> describe office;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| office_id  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| officename | varchar(100)     | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)


It's more clear entering table in more lines as below, with finishing with ; 

mysql> CREATE TABLE rack (
    -> rack_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> rackname VARCHAR(100));
Query OK, 0 rows affected (0.02 sec)


Some tables contain key(s) that are primary key for other tables. 

Example: vendor contact table has primary key from vendor table. Yes, there are more persons that are contact (work for) say, Dell. 

So data type for this  foreign key  is INT, UNSIGNED and NOT NULL. 

mysql> CREATE TABLE vendor_contact (
    -> vendcont_it INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> vendor_id INT UNSIGNED NOT NULL,
    -> vcname VARCHAR(100),
    -> vcphone VARCHAR(100),
    -> vcfax VARCHAR(100),
    -> vcemail VARCHAR(100));
Query OK, 0 rows affected (0.05 sec)


Also  default value  can be assigned to data, say you start buying only one license of software.  

mysql> CREATE TABLE software (
    -> software_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> swname VARCHAR(100),
    -> swlicense VARCHAR(100),
    -> swquantity INT UNSIGNED DEFAULT 1,
    -> swcomment VARCHAR(100),
    -> manufacturer_id INT UNSIGNED NOT NULL,
    ->  vendor_id INT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> describe software;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| software_id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| swname          | varchar(100)     | YES  |     | NULL    |                |
| swlicense       | varchar(100)     | YES  |     | NULL    |                |
| swquantity      | int(10) unsigned | YES  |     | 1       |                |
| swcomment       | varchar(100)     | YES  |     | NULL    |                |
| manufacturer_id | int(10) unsigned | NO   |     | NULL    |                |
| vendor_id       | int(10) unsigned | NO   |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)


All tables are created: 

mysql> show tables;
+---------------------+
| Tables_in_genesyshw |
+---------------------+
| hardware            |
| hwmodel             |
| hwstatus            |
| manufacturer        |
| office              |
| rack                |
| software            |
| team                |
| vendor              |
| vendor_contact      |
+---------------------+
10 rows in set (0.01 sec)


Quick check can be done with: 

# mysqlcheck -u root -p companyhw
Enter password:
companyhw.hardware                                 OK
companyhw.hwmodel                                  OK
companyhw.hwstatus                                 OK
companyhw.hwswlink                                 OK
companyhw.manufacturer                             OK
companyhw.office                                   OK
companyhw.officeracklink                           OK
companyhw.rack                                     OK
companyhw.software                                 OK
companyhw.team                                     OK
companyhw.vendor                                   OK
companyhw.vendor_contact                           OK


NOTE: 

Default MySQL engine is MyISAM. 

MySQL represents each table with .frm format definition. 

For MyISAM tables, storage engine creates data (.MYD) and index (.MYI) files.  

So your database files are as below: 

/var/opt/webstack/mysql/5.1/data/companyhw> ls
total 246
drwx------   2 mysql    mysql       1024 Dec 22 14:44 .
drwxr-x--x   5 mysql    mysql        512 Dec 22 10:52 ..
-rw-rw----   1 mysql    mysql         65 Dec 22 10:52 db.opt
-rw-rw----   1 mysql    mysql       9078 Dec 22 14:42 hardware.frm
-rw-rw----   1 mysql    mysql          0 Dec 22 14:42 hardware.MYD
-rw-rw----   1 mysql    mysql       1024 Dec 22 14:51 hardware.MYI
-rw-rw----   1 mysql    mysql       8608 Dec 22 14:29 hwmodel.frm
-rw-rw----   1 mysql    mysql          0 Dec 22 14:29 hwmodel.MYD
-rw-rw----   1 mysql    mysql       1024 Dec 22 14:51 hwmodel.MYI
-rw-rw----   1 mysql    mysql       8608 Dec 22 14:28 hwstatus.frm
-rw-rw----   1 mysql    mysql          0 Dec 22 14:28 hwstatus.MYD
-rw-rw----   1 mysql    mysql       1024 Dec 22 14:51 hwstatus.MYI
Etc, until you see all tables!


The myisamchk utility gets information about your database tables and also checks them. 

# myisamchk -d hardware

MyISAM file:         hardware
Record format:       Packed
Character set:       latin1_swedish_ci (8)
Data records:        0  Deleted blocks:       0
Recordlength:                  640

table description:
Key Start Len Index   Type
1   2     4   unique  unsigned long


Another way to check table and optimize if needed:

mysql> CHECK TABLE hardware EXTENDED;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| companyhw.hardware | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.01 sec)

mysql> OPTIMIZE TABLE hardware;
+--------------------+----------+----------+-----------------------------+
| Table              | Op       | Msg_type | Msg_text                    |
+--------------------+----------+----------+-----------------------------+
| companyhw.hardware | optimize | status   | Table is already up to date |
+--------------------+----------+----------+-----------------------------+
1 row in set (0.00 sec)


Okay, now we have database and want to add some data, as well as search for it. 

mysql> INSERT INTO office  SET officename='DCO';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO office  SET officename='MVO';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO office  SET officename='MARKHAM';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM office;
+-----------+------------+
| office_id | officename |
+-----------+------------+
|         1 | DCO        |
|         2 | MVO        |
|         3 | MARKHAM    |
+-----------+------------+
3 rows in set (0.00 sec)

mysql> SELECT officename FROM office;
+------------+
| officename |
+------------+
| DCO        |
| MVO        |
| MARKHAM    |
+------------+
3 rows in set (0.01 sec)

mysql> SELECT officename FROM office
    -> WHERE office_id=3;
Empty set (0.04 sec)

+------------+
| officename |
+------------+
| MARKHAM    |
+------------+
1 row in set (0.00 sec)


How about replacing data?

mysql> SELECT * FROM rack;
+---------+----------+
| rack_id | rackname |
+---------+----------+
|       1 | A1       |
|       2 | A2       |
|       3 | A3       |
|       4 | A4       |
|       5 | A5       |
|       6 | A6       |
|       7 | A7       |
|       8 | C1       |
+---------+----------+
8 rows in set (0.00 sec)

mysql> REPLACE INTO rack 
	SET rack_id='8', rackname='A8';
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM rack;
+---------+----------+
| rack_id | rackname |
+---------+----------+
|       1 | A1       |
|       2 | A2       |
|       3 | A3       |
|       4 | A4       |
|       5 | A5       |
|       6 | A6       |
|       7 | A7       |
|       8 | A8       |
+---------+----------+
8 rows in set (0.00 sec)


Check another way to replace values in a row. 
Note that REPLACE wants to insert new data, but when specifying existing primary key, the old data is replaced with new one. 

mysql> select * from vendor;
+-----------+--------------+----------------+--------------+-------------+-----------+--------------+------------+-------------+--------------+
| vendor_id | vendorname   | vendorphone    | vendorfax    | vendoremail | vendorwww | vendorstreet | vendorcity | vendorstate | vendorpostal |
+-----------+--------------+----------------+--------------+-------------+-----------+--------------+------------+-------------+--------------+
|         1 | Mojo Systems | 1-866-230-MOJO | 206-568-3010 | NULL        | NULL      | NULL         | NULL       | NULL        | 98144        |
+-----------+--------------+----------------+--------------+-------------+-----------+--------------+------------+-------------+--------------+
1 row in set (0.00 sec)

mysql> REPLACE INTO vendor
    -> VALUES (1, 'Mojo Systems', '1-866-230-MOJO', '206-568-3010',
    -> 'rjanson@gotomojo.com', 'www.gotomojo.com', '2411 South Walker St.', 'Seattle', 'WA', '98144');
Query OK, 2 rows affected (0.01 sec)

mysql> select * from vendor;
+-----------+--------------+----------------+--------------+----------------------+------------------+-----------------------+------------+-------------+--------------+
| vendor_id | vendorname   | vendorphone    | vendorfax    | vendoremail          | vendorwww        | vendorstreet          | vendorcity | vendorstate | vendorpostal |
+-----------+--------------+----------------+--------------+----------------------+------------------+-----------------------+------------+-------------+--------------+
|         1 | Mojo Systems | 1-866-230-MOJO | 206-568-3010 | rjanson@gotomojo.com | www.gotomojo.com | 2411 South Walker St. | Seattle    | WA          | 98144        |
+-----------+--------------+----------------+--------------+----------------------+------------------+-----------------------+------------+-------------+--------------+
1 rows in set (0.00 sec)


Let's now use JOIN and get data from 2 tables at once (using simple Inner Join). 

There is table with vendors and vendor contacts. 
Vendor table doesn't have contact name, and vendor_contact table shows vendor_id (foreign key) but now vendor name. 
So JOIN helps me that in one search I see where people work. 

mysql> select * from vendor;
+-----------+--------------+----------------+--------------+----------------------+------------------+-----------------------+------------+-------------+--------------+
| vendor_id | vendorname   | vendorphone    | vendorfax    | vendoremail          | vendorwww        | vendorstreet          | vendorcity | vendorstate | vendorpostal |
+-----------+--------------+----------------+--------------+----------------------+------------------+-----------------------+------------+-------------+--------------+
|         1 | Mojo Systems | 1-866-230-MOJO | 206-568-3010 | rjanson@gotomojo.com | www.gotomojo.com | 2411 South Walker St. | Seattle    | WA          | 98144        |
|         4 | Sun          | NULL           | NULL         | NULL                 | NULL             | NULL                  | NULL       | NULL        | NULL         |
|         5 | open source  | NULL           | NULL         | NULL                 | NULL             | NULL                  | NULL       | NULL        | NULL         |
+-----------+--------------+----------------+--------------+----------------------+------------------+-----------------------+------------+-------------+--------------+
3 rows in set (0.01 sec)

mysql> select * from vendor_contact;
+-------------+-----------+---------------+--------------+-------+-------------------+
| vendcont_it | vendor_id | vcname        | vcphone      | vcfax | vcemail           |
+-------------+-----------+---------------+--------------+-------+-------------------+
|           1 |         1 | Ben MacDonald | NULL         | NULL  | bmac@gotomojo.com |
|           2 |         4 | Allen Lee     | 415.608.8548 | NULL  | allen.lee@sun.com |
|           3 |         4 | Ramil Nick    | 650-863-2491 | NULL  | NULL              |
|           4 |         4 | Amor          | 650-296-0461 | NULL  | NULL              |
+-------------+-----------+---------------+--------------+-------+-------------------+
4 rows in set (0.01 sec)

mysql> select vendor_contact.vcname, vendor.vendorname
    -> from vendor
    -> inner join vendor_contact
    -> on  vendor_contact.vendor_id = vendor.vendor_id;
+---------------+--------------+
| vcname        | vendorname   |
+---------------+--------------+
| Ben MacDonald | Mojo Systems |
| Allen Lee     | Sun          |
| Ramil Nick    | Sun          |
| Amor          | Sun          |
+---------------+--------------+
4 rows in set (0.06 sec)


Another search with JOIN tells me location of servers.  

mysql> select rack.rackname, hardware.hwcomment 
    -> from rack
    -> inner join hardware
    -> on rack.rack_id = hardware.rack_id;
+----------+-----------+
| rackname | hwcomment |
+----------+-----------+
| A3       | java      |
| A3       | castro    |
| A4       | gerda     |
| C10      | agate     |
| C11      | camel     |
+----------+-----------+
5 rows in set (0.01 sec)


MySQL backup

Quick way to backup database is:

# mysqldump -u root -p --opt companyhw > companyhw.sql


Backup (which is company.sql file) contains SQL statements to re-create tables and populate them with data). 

--opt is shorthand for many options like:  
--add-drop-table
 --add-locks 
--create-options 
--disable-keys 
--extended-insert 
--lock-tables 
--quick
 --set-charset 
(check man page for more explanations). 

Ops, I just deleted my databases. 

mysql> DROP DATABASE
    -> companyhw;
Query OK, 10 rows affected (0.43 sec)


Let's restore it.

# mysql --verbose -u root -p companyhw < companyhw.sql
Enter password:
ERROR 1049 (42000): Unknown database 'companyhw'


Hm, so I need first manually to create database "companyhw". 

mysql> CREATE DATABASE companyhw;
Query OK, 1 row affected (0.09 sec)


Try same command now again: 

#  mysql --verbose -u root -p companyhw < companyhw.sql 
Enter password:
--------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
--------------
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
Etc


And the database is back to the business.