I too have been interested in this subject for a long time. Here's my take on it:
==============
Metatile Chunks
==============
Many game artists / level designers back in the day of the NES (and SNES / Genesis especially) used tile "chunks" to build their levels. These "chunks" were simply groupings of individual (smaller) tiles arranged in a grid blob of varying numbers of tiles wide and tall -- generally they were always square or rectangular. These blobs would build large things like trees or statues or columns and arcs, etc. so that they could be quickly placed in the levels. Each individual tile in these chunks could have flags set as to their solidity and other properties (i.e. one or two parts, for example, of the larger tile chunk is two parts [the higher end and the lower end] of a two-tile-wide slope). The Sonic games are an extreme example of this tile system at its most functional.
That being said, if you were an artist back in that day, you might have designed your assets in large squares or rectangles to help you establish a mood. This applies to everything from RPG tiles to platformer games on the SNES, for example, because any sort of scenery could be drawn in those large square/rectangle chunks as long as they fit in the SNES's sprite/tile 'atlas' on the cartridge and were able to be stored in memory as the game was running.
I think these tile "chunks" might have also been called "metatiles" but I'm pretty sure the terminology varied from company to company since most of that stuff was proprietary back then.
======================
Multiple 'Canvas' Tiles
======================
The main idea of the "tile-chunk" approach is to draw out your world in many smaller "canvases" (larger than a few tiles in width and/or height usually) on the same tilesheet when you need to have lots of large and varied scenery (such as in platform games) -- especially if your scenery must NOT look repetitious and you have memory to spare. An extreme example of this is the backgrounds in oldschool arcade beat-em-ups like the Simpson's Arcade Game or any number of other Konami titles in arcade machine cabinets.
======================
Modularized Metatiles
======================
If you
don't have memory to spare
or if you need lots of tiling variations (i.e. in the case where you have many jointed pieces and angles with lots of shape/style variations that need to remain modularized), you would establish a base texture for them, and develop all tile variations from that (which ultimately will bring us back to all those single RPG grass-tile tutorials found all over the net.)
The modularization technique, contrary to popular belief, does NOT always have to be applied to a single tile -- it can also be applied to a larger tile-chunk / meta-tile in order to make it appear tileable. In actuality, different games use different methods (single-tile vs. meta-tile variations) for modular tile variations.
Star Ocean: Blue Sphere on the Gameboy Color is a great example of using modular tiles and tile chunks together to represent amazing-looking worlds while keeping the modular tiles extremely varied and tileable. There's a thread here on Pixelation that details some of the tilesets in the game. You'd be amazed at how tiny the tiles are that are used in some of the meta-tiles, and yet it's because of their granularity that they are able to be so flexible -- they can be used to build much larger and more varied assets with much less memory.
===================
Tileset Design
===================
How you design your tileset really boils down to how you ultimately choose to derive variation in your tiles. An individual tile (or metatile) can only ever be either A)
modular and flexible or B)
unique but static and there really isn't any inbetween for a single tile in a tileset.
It's important to keep both approaches in mind. In most cases -- at least in the early days, where the SNES and Genesis were involved -- performance was most often prioritized over graphics artistry since the 'fancy' hardware was still pretty new to programmers. Eventually, graphics became a higher priority once the underlying technology and performance issues were better understood. Once tile count stopped being as much of an issue anymore, metatiles were utilized a lot more often -- however, these were almost always still used alongside oldschool tiling / modularization techniques to stretch the number of tiles that could go toward forming larger (metatile) tile-chunks.
===================
The Oldskool Approach
===================
Even with today's virtually unlimited memory, if you want to go with a grid-based graphical layout for your levels, you'll generally
still use both types of tiles, just with modular tiles being more geared to terrain or pipes or anything with variable size/shape/configurations throughout your levels.
In design cases like that of the tree in your screenshot, tile chunks were specially created for the bulk of the tree -- and, actually, the lighting was done by hand by the level designer (and
not the tileset artist) using some very much rudimentary tiles provided by the artist. I'll give an example:
Here you have the original image, now aligned to a 16x16 grid (most likely what the level designer used when designing this level in the game):
Below (in yellow) are the unique tiles and (in blue) are any flipped and/or tiles repeated more than once in the image in various places that were used to achieve the lighting effect here (with their respective yellow tiles highlighted in yellow elsewhere):
This effect is mostly a transparent overlay of tiles that outline the tree tiles and also attempts a 'cutout' effect for the cast shadow effect to simulate a halo of light and a shadow. I can guarantee no painterly layout was used for this process. Just a slick level designer using some tiles haphazardly made by the tile artist to achieve this effect. It's very likely the two worked closely together and the level designer said "hey, it might be cool to give this tree some lighting" and the artist said "yep, I'll throw some tiles your way to see how it looks" and thus you got the image above.
==============
A case study
==============
Put the images above on a 16x16 grid (switch to an 8x8 grid for the smaller tiles) and you'll see the tiles yourself.
After studying those images (perhaps as frames in Graphics Gale you flip back and forth between) you'll see just how haphazardly the tiles were drawn -- as stated before, much more focus was given to drawing them modularized than designing a specific 'mood' for the level.
Even the lighting on the window was simply reused overlay lighting tiles, from down beneath/around the tree's root area. The key to this design is the level designer was simply creative in how he used the existing tiles to achieve the effect he was going for. You'll find that many games in the past (and even today!) still rely on creative level designers to utilize the provided modularized art assets in creative and interesting (and sometimes in even unintended!) ways!
==============
The Takeaway
==============
My advice is to stop worrying so much about what your assets look like and instead focus on what you can do with them. The main question you need to answer is this --> Do the assets I have serve my intended purpose for this level? If not, follow up with this --> What assets do I still need to accomplish my vision of this level?
In the end, if you don't have a strong vision for a level, no amount of drawing fancy-looking tilesets will save it.
