There are many excellent and interesting sample databases available, that you can use as a template (or pattern) to design your own databases. Show MySQL's Sample Employee DatabaseReference: MySQL's Sample Employees Database @ http://dev.mysql.com/doc/employee/en/index.html. This is a rather simple database with 6 tables but with millions of records. Database and TablesThere are 6 tables as follows: Table "employees"CREATE TABLE employees ( emp_no INT NOT NULL, birth_date DATE NOT NULL, first_name VARCHAR(14) NOT NULL, last_name VARCHAR(16) NOT NULL, gender ENUM ('M','F') NOT NULL, hire_date DATE NOT NULL, PRIMARY KEY (emp_no) ); There are 300,024 records for this table. Table "departments"CREATE TABLE departments ( dept_no CHAR(4) NOT NULL, dept_name VARCHAR(40) NOT NULL, PRIMARY KEY (dept_no), UNIQUE KEY (dept_name) ); The keyword There are 9 records for this table. Table "dept_emp"Junction table to support between many-to-many relationship between CREATE TABLE dept_emp ( emp_no INT NOT NULL, dept_no CHAR(4) NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, KEY (emp_no), KEY (dept_no), FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE, PRIMARY KEY (emp_no, dept_no) ); The foreign keys have There are two reference actions: There are 331,603 records for this table. Table "dept_manager"join table to support between many-to-many relationship between CREATE TABLE dept_manager ( dept_no CHAR(4) NOT NULL, emp_no INT NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, KEY (emp_no), KEY (dept_no), FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE, PRIMARY KEY (emp_no, dept_no) ); There are 24 records for this table. Table "titles"There is a one-to-many relationship between CREATE TABLE titles ( emp_no INT NOT NULL, title VARCHAR(50) NOT NULL, from_date DATE NOT NULL, to_date DATE, KEY (emp_no), FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, PRIMARY KEY (emp_no, title, from_date) ); There are 443,308 records for this table. Table "salaries"Similar structure to CREATE TABLE salaries ( emp_no INT NOT NULL, salary INT NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, KEY (emp_no), FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, PRIMARY KEY (emp_no, from_date) ); There are 2,844,047 records for this table. Stored ObjectsNo stored objects (view, procedure, function, trigger, event) defined. [Shall try!] MySQL's Sample Salika (DVD Rental) DatabaseReference: MySQL's Sample Sakila Database @ http://dev.mysql.com/doc/sakila/en/index.html. The MySQL's Sample Salika (DVD Rental) Database can be downloaded fromhttp://dev.mysql.com/doc/sakila/en/index.html. It is a complex database with 16 tables. It also illustrates features such as Views, Stored Procedures and Triggers. This is probably the best sample available for studying MySQL databases. Database and TablesAll the tables have For UTF8 support, we could set the SET NAMES 'utf8'; DROP DATABASE IF EXISTS `sakila`; CREATE DATABASE IF NOT EXISTS `sakila` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; USE `sakila`; We could use " Table "actor"CREATE TABLE actor ( actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (actor_id), KEY idx_actor_last_name (last_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
There are 200 records for this table. Table "language"Languages: such as English, Italian, Japanese, Mandrain, Cantonese, French, German. CREATE TABLE language ( language_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(20) NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (language_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; May be simpler to use an There are 6 records for this table, i.e., Table "film"CREATE TABLE film ( film_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, description TEXT DEFAULT NULL, release_year YEAR DEFAULT NULL, language_id TINYINT UNSIGNED NOT NULL, original_language_id TINYINT UNSIGNED DEFAULT NULL, rental_duration TINYINT UNSIGNED NOT NULL DEFAULT 3, rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99, length SMALLINT UNSIGNED DEFAULT NULL, replacement_cost DECIMAL(5,2) NOT NULL DEFAULT 19.99, rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G', special_features SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (film_id), KEY idx_title (title), KEY idx_fk_language_id (language_id), KEY idx_fk_original_language_id (original_language_id), CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
There are 1000 records for this table. Table "film_actor"Junction table between CREATE TABLE film_actor ( actor_id SMALLINT UNSIGNED NOT NULL, film_id SMALLINT UNSIGNED NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (actor_id, film_id), KEY idx_fk_film_id (`film_id`), CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; There are 5462 records for this table. Table "category"CREATE TABLE category ( category_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(25) NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (category_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Table "film_category"Junction table to support many-to-many relationship between CREATE TABLE film_category ( film_id SMALLINT UNSIGNED NOT NULL, category_id TINYINT UNSIGNED NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (film_id, category_id), CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category (category_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; There are 1000 records for this table. Each of the 1000 films has ONE category. Table "film_text" - FULLTEXT Index and SearchCREATE TABLE film_text ( film_id SMALLINT NOT NULL, title VARCHAR(255) NOT NULL, description TEXT, PRIMARY KEY (film_id), FULLTEXT KEY idx_title_description (title, description) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
There are 1000 records for this table. Each Table "inventory"The company could have many copies of a particular film (in one store or many stores). Each copy is represented by an CREATE TABLE inventory ( inventory_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, film_id SMALLINT UNSIGNED NOT NULL, store_id TINYINT UNSIGNED NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (inventory_id), KEY idx_fk_film_id (film_id), KEY idx_store_id_film_id (store_id, film_id), CONSTRAINT fk_inventory_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_inventory_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; There are 4581 records for this table. Table "store |