Designing databases on MySQL?

Post Reply New Topic

I was playing around with my Linux system tonight, and managed to get MySQL 5.1 up and running. Got it connected to the Windows system and managed to build a couple of tables before running out of ideas.

My mind is trying to make the jump to client-server databases and there's a lot of new things to learn. For instance, I'm not sure how to create relationships between two tables. Can anyone recommend a good reference book or website?

http://www.samspublishing.com they have a book called learning SQL in 24 hours or something like that, that should help you out.

MySQL 5.1 was only released recently, so there's not likely to be many books on it yet. Generic SQL books will help, but won't teach you how to get the most out of MySQL. The MySQL web site has a good set of on-line training material.

http://dev.mysql.com/doc/

What are you using on the Windows side? Not Access, I hope.

Thanks for the leads, am following up!

My initial client-side program was the database component from OpenOffice.org 2, and I also have MySQL Administrator installed on the Windows system. Ultimately I'm planning to learn how to write web applications, but along the way I'm going to experiment with any program that's capable of reading remote databases. My current learning priorities are:

1. Re-learning all I've forgotten about SQL itself;

2. Building a fairly complex data set that uses foreign keys;

3. Learning how to write server-side stored procedures, which I understand is a major factor in the speed difference between server-side and client-side processing.

(Access... *ack* ...Worked with it for far too long.)

Yesterday I picked up the MySQL Pocket Reference from O'Reilly and took a quick glance at a couple of the larger books. Once my SQL vocabulary is back in action, I'll have a better idea of which book will be the best fit for me, and in the meantime the web docs will keep me going.

A caution about foreign keys: as far as I know, MySQL only does constraint checking in one direction. If you add or edit a child record in a table that has a foreign key then it will check to make sure that the parent record referenced by that key exists. If you edit or delete the parent record, it does not trigger a cascade update or cascade delete in the child records. It's up to you to recurse the relationships and clean up the children yourself.

The other thing to remember for later on is that MySQL supports many different types of database storage (BerkleyDB, MyISAM, InnoDB, etc.) but the InnoDB format is the only one that uses a transaction log. The BEGIN and COMMIT commands will not generate errors when you use other database formats, but ROLLBACK will fail.

Last but not least, the "auto increment" values in a table are not tracked in a non-user table as they are in Postgres, but instead are determined on the fly by simply adding 1 to the highest index value currently in the file. That can cause problems if you assume that the key value for a deleted record won't be reused.

MySQL was built for speed, but you need to be careful with it. Postgres is a much more full-featured database but generally runs a lot slower.

Both of these are incorrect.

Only InnoDB supports foreign keys, but it does enforce constraints both ways. You can set up foreign keys with various options so that deleting a parent record with a related child will: prevent the parent record from being deleted if a corresponding child exists, delete the child record, set the child's parent ID value to NULL, or do nothing. Same with updates.

Auto-increment doesn't behave that way and never did to my knowledge, unless things were different before I started using it at version 3.23. If you have values of 1,2,3,4,5 and delete #5, the next autoincrement value will still be 6.

BTW, for setting up databases, I'd highly recomment phpmyadmin. It is an excellent administration tool. I've designed many very complex database structures, and it's all I use.

wow, i use mysql everyday and most of that went over my head!! !! !! !

(jamie goes and reads all his mysql books again)

jamie