In MySQL's version of SQL, the notation foo.bar means "database" foo, table "bar". "Tablespace" is not the correct term here.
There is one level for organizing things -- a "database" is a collection of "tables". (Yes, x.table1 and y.table1 can be tables with the same name with either the same or different schema.)
> the contents of the database must be dumped out and stored somewhere on my hard drive, obviously when the MySQL server starts back up, it has to be able to re-read in the data making up my database.
This sounds more like a migration task -- dumping the data from one server, then loading it on another. Normally, one builds database(s) and table(s) on disk and leaves them there forever.
> everything, are contained with a single .mdb or .accdb
MySQL does not work that way. The 'everything' is in a bunch of directories and files. All are (normally) hanging under one directory.
> it just read the .sql files in to repopulate the database
Ouch! How grossly inefficient! That would work 'ok' for tiny databases, but imagine if you had a terabyte of data.
I don't know the details of Navicat, but I would assume there is an ordinary, persistent, MySQL data tree under the covers.
> between .sql files and the .ibd files.
.sql files are (normally) text files that contain SQL statements such as CREATE TABLE and INSERT statements. Such a file can be used to (re)load a database(s). Use type/cat/more/vi/... to see what the file looks like.
.ibd files is the 'tablespace' for _one_ _table_. It contains all the data and indexes for that table. Next to it will be a .frm file that contains the schema (an encoding of the CREATE TABLE). Neither of these files is readable by us mere mortals.
> say that the tablespace name is probably used to differentiate where the files are stored on the hard drive (folder names, perhaps?)
Close. You will find a directory (ie, folder) for each _database_ (not 'tablespace') that you have.
Note, it is ok to build a system with just one database; it is also ok to build a system with dozens of databases. It all depends on the structure of the data and your application. Likewise, a database may contain one table, or many.
> it needs to save two table1.ibd files, surely they both can't live in the same directory/folder.
Very true.
> InnoDB ... sounds like an evil Swedish nemesis
The inventor was actually Finnish. The original name was Innobase, but that had trademark problems, or something. (OK, now we are getting far into trivia.)
http://www.zoominfo.com/p/Heikki-Tuuri/283692256
> P.S. I had to laugh..."Superfreak" came into my head. :)
Such references are dying off (as did he).