I have also been informed that it's quite the science knowing how to structure the database itself, including what to index, to make sure it is of a desired size (doesn't bloat), performs at a reasonable speed

A subject you should spend a bit of time reading up on before you design the database is database normalization. A rough description is available here.

It's covered in the meatier Sql books and I'm sure a Google will get you going too. I can't offer much more than that; all my books are packed ready to move house.

I think you need to strike a balance between learning 'enough' about normalization to 'get by' and spending too much time becoming an expert on this sub-topic of database design.