In SharePoint MOSS2007, there are some interesting things I've learned about Master Pages and Custom Master Pages.
1) On a site's Settings / Look and Feel / “Master Page”...
Site Master Page is stored in the database webs.CustomMasterUrl column,
System Master Page is stored in the database webs.MasterUrl column.
I am still trying to figure out the CustomMasterUrl vs MasterUrl relationship, but at least now I know the two Settings Master Page values represent those two values! Oh, and in my reckless experimentation, I discovered that if you change those values in the database properly (to proper master page values), the changes take effect on the site's look immediately on the next page load (ie: changing the database directly works).
2) Using a Feature, I can add master pages to the portal's Master Page Gallery (/_catalogs/masterpage/) such as mydefault.master. Then I can modify any site template to use the Master Page by modifying the TEMPLATE\SiteTemplates\sts\XML\ONET.XML file. All new sites will use that master page. It doesn't work right unless you include that leading slash before _catalogs. This seems to tell the MOSS2007 site to use the master page over in the top level site, instead of it's own masterpage gallery.
Example:
3) You'll usually see these errors in Settings / Look and Feel / Master page of a new site (ex: a Team site) until you enable the Publishing Feature for the site (even on a fresh install).
The site master page setting currently applied to this site is invalid. Please select a new master page and apply it.
The system master page setting currently applied to this site is invalid. Please select a new master page and apply it.
See: http://support.microsoft.com/default.aspx/kb/936908
4) When you migrate a site from SharePoint 2003, the masterpage set in the site template (ex: in a modified sts template) does not get automatically used in the migrated site. Instead, the migrated site ends up with a MasterUrl of “/sitepath/_catalogs/masterpage/default.master“ and the CustomMasterUrl of “/sitepath/_catalogs/masterpage/default.master“ (unless the site was a meeting workspace, in which the CustomMasterUrl is “/sitepath/_catalogs/masterpage/mwsdefault.master“). I really wanted to see the old STS site take on the properties of a new MOSS2007 STS site template. Oh well.
Final thought: This is all just experimentation, and I'm a bit concerned that I had to modify the site template (site definition) to make the migrated sites come across with the new master page (which they didn't anyway). Also, even though it works, don't modify the SharePoint database directly! (wink, wink). So in other words, don't do anything I suggested here, unless, maybe, if someone makes you.