I like the idea of a database that gradually increases in complexity as you progress through various training exercises in it.
Start out with a table of customers, a table of salespeople, a table of products, and a table of orders, and perhaps a many-to-many join between orders and products. Very simple structure, just enough to get an idea of what a relation (table) is, what normal forms mean, and how simple foreign keys work. Something a real business could use as a starting place for replacing a file cabinet of paper order forms. Start out with contact data in the customers table, to illustrate the weaknesses of that violation of normal form, but still make the database human-understandable.
Then, with a script that encompasses the necessary DDL and insert tasks, add in inventory and a running totals problem, maybe add in employee hierarchies, and perhaps a more complex way to track customer contact data, such as a table of addresses with customer ID and address type, separation of address and phone and e-mail, and so on. Lesson covers running totals, adjacency hierarchies, and higher normal forms.
Another script with necessary DDL and DML, and you morph the database to include billing data including encrypted credit card numbers, more employee data including encrypted SSNs (or whatever), more complex database security access including logins and groups. Lesson is on security and encryption.
And so on, one script per set of lessons, with the database getting more and more complex each time. It would be easy enough to build that way, and would progressively increase SQL skill and knowledge. Would be slightly more complex to make sure that no script was dependent on any other script having already been run, unless it is a more advanced lesson specifically building on the prior lesson. E.g.: Security 2 could be dependent on Security 1, but independent of Normal Forms 1.
Does that sound like a good compromise between starting out simple and still being able to get the most complex and advanced subjects in there?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon