Newbie: Guide to schema conventions

[Paul Gardner]

Hi,

I am an entirely self-taught PHP/MySQL developer and have been developing web applications for over 6 years now - previously a systems analyst.

I have always used phpMyAdmin to create my databases and have never done any major planning of schemas before creating a database and adding tables. I do nothing complex with MySQL other than defining primary keys, auto increments and some fulltext indexing.

Over the last few years I have became increasingly aware that whilst my code works well for the jobs I undertake, one day (hopefully soon) I will employ a trained/experienced developer who will probably take one look at my database architecture and coding and want to start it all again from scratch.

I am addressing the bad habits in my PHP coding by using the CakePHP framework which has introduced me to the Model-View-Controller design pattern and involves using solid conventions when creating a dbase schema.

I was referred to Workbench (using the community version for now) via a CakePHP article on dbase schemas and I understand the creation of tables and relations, but I don't have a clue what Views, Routines, Routine Groups and Layers (in ERR diagrams) are.

Is there a good tutorial anywhere that will explain schemas to me in more detail and cover these topics?

Thanks,

Paul
Webbed IT