Practical Aspects of Making Changes to Databases
A really important thing to remember about SQL databases is that architecture is king, it governs how difficult everything else is, from querying to modifying its structures later. Business requirements will sometimes force you to avoid what is traditionally best practice or break a particular design pattern. It's okay that it happens, but certain patterns can make your life a lot easier.

For the most part we rely on using traditional development tools to manage our database design manually. I personally work almost exclusively in Visual Studio and SQL Server Management Studio for MS SQL Server. We use VS to manage our SQL Server project within our larger product code base, utilize our TFS Source Control, and use the comparison tools to generate patch scripts. I do most of my scratch pad development, testing, and management of our local development DBs via SSMS. For 3rd party tools I prefer Redgate's SQL Prompt and SQL Search, not particularly cheap though. They have some other cool toys, but those are the only two I use every single day; to the point that I feel sluggish without SQL Prompt. Though the latest version of SSMS seems to have improved its intelli-sense to almost the same level of SQL Prompt.

Adding to a database structure (especially in a well designed relational model) is almost always easier than modifying or removing something from said structure. Creating new tables and relationships doesn't pose a risk to any of your existing data or queries since it's all additive. Worst case you'll have to populate some of those new tables with existing data. On the other hand modifying or deleting existing columns and tables may require you to migrate data or modify queries to maintain data integrity. It'll be tempting to be lazy about this sometimes, don't be! You'll thank yourself later.

I personally believe good database design is a matter of keeping things relatively simple and adapting when necessary: using Foreign Keys, naming columns after their FK target field, maintaining constraints, indexing high volume tables, relying heavily on INNER JOIN relations, and avoiding 'negative' logic (NULLs, NOTs, implicit relationships, ORs, etc...) when possible can make your life a lot easier to maintain your database over time and will get you most of the way through most projects. We easily spend 25-50% of our time undoing 'bad' design decisions or lazy workarounds from previous and less experience developers. 

Unfortunately this is only a very high level view, we could talk about specific topics for days. I would definitely recommend poking around various blogs and picking up a book or two to get a grasp of some of the things I've mentioned above.