Relationships in MySQL [help and/or listen to me rant]

OK this matches up with what I thought I knew. I was just wrong about how much of “it” was actually inside MySQL versus how much was done in PHP.

OK, here’s the deal. Right now I am a FileMaker developer. If you advertise that you want a database developer to “build a database” or “maintain, expand the capabilities of, and add new reports to” or otherwise do whatever with your database, and it’s a FileMaker database, I’m your guy. If you call me as a consultant and say “Hi we dont know diddly about databases, we need one to manage our company, can you create it for us and tell us what we need and get us up & running?” I can do that.

My goal is to expand on that outside & beyond FileMaker because the overwhelming vast majority of job ads and opportunities are about SQL and PHP systems. I need to know WHAT IT IS that I need to know in order to be able to do all that except not in FileMaker but in SQL and PHP. (and/or Crystal Reports or Brio or Ruby or what-the-hell else ever). Please tell me what it is that I have to master to be able to replicate the functionity I can provide in FileMaker, in more “mainstream” db environments – ??

PS: where does Microsoft SQL fit into all of this? Would training on that help me, be a useless side trip, be useful but as a very different thing from MySQL + PHP, be 90% the same fundamentals and therefore a good thing to get, or what?

It sounds like you need to pick up SQL for Dummies/Teach Yourself SQL in 21 Days/etc. for an introduction to the language. All the major database systems will use something close to what any introductory book covers, and once you’re comfortable with one, it’s not too bad to pick up the others.

Regardless of specific platform/environment, sounds like you need to understand SQL and what the traditional databases do (they are all pretty similar, although based on what I read, MySQL is lacking some of the features that Oracle, DB2 and SQL Server have).

Small subset of things to learn:
Tables (you know this one)
Stored Procedures
Locking mechanisms (each DB is different here with big performance/accuracy implications/tradeoffs)

I’ve worked mostly in MS SQL Server and a little in Postgres. Generally I’d say MS SQL is fairly approachable, lots of online help, and has a large demand for skilled devs (though I’m in Seattle, so probably biased). It doesn’t have near as many quirks as I’ve heard about in MySQL. For just learning you would probably want the express edition (since its free) and I’m not familiar with the exact tools it offers.

All in all though, SQL is SQL. You can probably learn the heart of it from any database installation… Migrating to another is just a week or two of practice and doc reading. So, just find one where you enjoy the environment and the tools at hand. If you like Command line stuff you’ll probably love PostGres, if you are a mouse person, SQL Server is probably more your thing.

Just some other general advice to add to RaftPeoples list:

Pick PHP or pick databases, but not both. There is just too much to learn

Know the difference between understanding databases and understanding SQL. SQL Just happens to be a fairly standard way of getting info from a database. It won’t really help you optimize for speed or make a data model that doesn’t suck.

If you don’t already, learn normal forms. You probably don’t need to know what separates 3NF from 4NF, just enough to make a good table structure. Sorry if this is obvious, I still get a lot of applicants who can write a crazy SQL statement but glaze over when asked to design a simple schema.