Why is this needed?
“world” database current sample is 3 tables, cities, countries and langs.
Not the Intellectual Property of MySQL, so it can’t be bundled with the software
No table type diversity, not a lot of data type diversity
No MySQL 5 features
Not a lot of data
Not really used in tutorials and articles because it’s not robust enough
Basically, needed a sample database that was new, that allowed for many queries.
Used an idea from a Dell Sample DB
It’s 2 stores, in 2 different countries.
Designing the Database
Designed with MySQL Query Browser
Community provided feedback and content
Trick was balance between simplicity and completeness
Learning db, so people need to understand it
After schema was approved, data was loaded
Stored procedures, triggers, UDF’s and VIEWS were used
2 files in installation — one for schema & routines and one for data, so you can just look at schema if you want.
16 tables, 8 views,
Data is never deleted — it’s marked as deleted, but not actually deleted, for data integrity reasons.
Contains most (all?) data types, including SET and ENUM.
VIEWS used to simplify queries. ie, there’s a table for actors and one for films, so actor_info contains a list of type of movie and name for each actor. So it would show a list of actors with a list of films that they’ve done — 1 row per actor, and a comma-separated list of films they’ve done (sorted by type).
There are a lot of good views in there, which show that that’s what you should do for popular queries (like “show me all the movies that are rented, by customer”).
For common procedures — ie, is a film in stock?
get_customer_balance — it does what you think it does.
There’s one to sync 2 tables, for example.
PostgreSQL want to do
Documentation copyright MySQL
Goals Going Forward
Make as few changes as possible
Implement 5.1 changes if necessary
Download it at: http://www.openwin.org/mike/download/sakila-0.8.zip