Some of the limitations of NES hardware and common mapper hardware severely limit the system's capability to perform well in some genres. This could be part of why they took off after 1991 when more powerful hardware became more readily available.
To clarify something: Some of these limitations don't mean "can't" as much as "too expensive". In a commercial game project, managers have to balance the cost of solving technical problems like those listed below with the cost of exploring, implementing, refining, and balancing the game rules. A game for a powerful system will often be programmed inefficiently because it gets the game out the door faster. Building your own clone (or emulation device) of mappers and other external devices is also possible, although this may also be expensive and/or time consuming.
A clone or a port to a less powerful system keeps most of the game design, which is already paid for, and cuts down the design in ways that the porting team thinks the players won't care about. A team of amateurs with no deadline can eventually squeeze more capability out of a system than can a commercial game developer bound by opportunity cost and return on investment. Even commercial developers in a country with a low cost of living, such as China or Brazil compared to Japan or the United States, have a time-money tradeoff biased toward time: witness the "Hong Kong Original" Famicom reductions of 16-bit fighting games, platformers, and RPGs, and the Brazilian ports of games to Sega Master System and Sega Genesis.
Limitations: 8 KiB PRG RAM; no 3D graphics hardware
Some kinds of simulator games, like SimCity, The Sims, Harvest Moon, and Animal Crossing, have large grid-based world maps and tend to need 8 KiB just to store this map, let alone the rest of the world's state. Very few NES cartridge boards (such as SXROM and EWROM) provided more than 8 KiB, and most of them were rare and/or Japan-only. Nintendo has been known to cancel finished products such as the NES port of SimCity and the English version of Mother because replication cost would kill the return on investment. Games would have to be planned carefully to fit their state into a cheap chip. Nowadays, it's possible to make a custom mapper on a CPLD that bankswitches a large work RAM and saves to an unused area of the PRG flash.
Games that allow the player to customize his appearance extensively, as in Animal Crossing and The Sims, typically rely on 3D texture mapping so that the changes to the appearance can be seen from all angles. Tony Hawk series for Game Boy Advance soft-rendered a 3D player character in real time, but the GBA CPU is also fast enough to emulate the NES CPU. An NES game with customizable characters, such as Cocoron, has to store each animation of each body part seen from each angle and composite them at runtime, either drawing them into CHR RAM or overlaying numerous sprites (and risking flicker).
Limitations: Input devices, mostly
Games that allow for modification, such as modified game graphics, maps, and scripts, tend to be extremely limited. Part of this has to do with input limits: the NES never had an official alphabetic keyboard or precise pointing device. In Japan, Family BASIC shipped with a keyboard, and the Oeka Kids paint program shipped with a graphics tablet, but these use the Famicom's DA15 expansion port, which the NES doesn't have. Videomation, a paint program for the NES with a unique cartridge board, has to use a clunky control scheme similar to the speed control in Image Fight. (Its animation is also severely limited compared to Mario Paint for Super NES, and it can't save.) There is a well-documented hack to use the Super NES Mouse with homebrew games on the NES or AV Famicom, but that's about all there is.
There are some external devices which can be used for extended save data: the Famicom Disk System, the Family BASIC Data Recorder, the Battle Box, and the Turbo File. However, all are Japan-only, exceptionally rare, and the latter two only hold 8KiB of data anyway.
Limitations: No 3D graphics
There are two ways to do a driving game: behind-the-car with a Pole Position style track (as seen in F-1 Race and Rad Racer) and overhead/isometric style (as seen in RC Pro-Am and Micro Machines.) In a behind-the-car view, the car can't turn all the way around. The "morphmation" technique used to bend the racetrack limits the variety of scenery and track layout: for example, the track can't have hairpin turns or narrow segments without using an obscenely large multi-bank CHR ROM like that seen in Cosmic Epsilon. Overhead view severely limits how far ahead one player can see, resulting in usually slower movement, and the camera generally has to follow one player or the other. (Micro Machines uses the screen edge as a game mechanic.)
Limitations: 16x16 pixel attribute tiles
A lot of tile-matching games, such as Columns (ported to NES as Magic Jewelry and Mystic Pillars), Yoshi's Cookie, Puyo Puyo, Wario's Woods, and Palamedes, have 16x16 pixel tiles. This limits each player's playfield to about 6x12 cells. Games with smaller tiles typically can't go over three unique colors, as seen in Dr. Mario and Tetris 2. One compromise involves drawing tiles in both players' playfields as a dithered combination of orange, green, and blue, much as CGA graphics were limited to three colors plus the backdrop. It's not as clean as a Sega Master System game, but it gets the job done without having to destroy one of 13 donor games to use MMC5 ExGrafix. Another workaround would be to make a custom mapper on a CPLD that simulates some subset of ExGrafix. Klax uses a mapper with a scanline counter to change the scroll position in mid-frame, making its 16-pixel-wide attribute areas less than 8 scanlines tall.
Yet another thing that can be done is to make the single-player playfield wider than the 2-player playfield. This was tried in things like Magical Drop 2, Wario's Woods, and the 4-player "Familiss" mode of BPS's Super Tetris 3. Lumines for PSP borrows the mechanic of two players fighting over space in a single playfield from FantaVision for PS2, and at least one fan-made clone of the single-player game Zoop makes the game cooperative with two shooters on one big field.
If both players' graphics are drawn as sprites, characters will have to be no wider than 32 pixels. Otherwise, they'll flicker like crazy when you cross the 8-sprite limit. You can get away with wider jumping poses because it's less likely that both characters will be in the air at once. Shaq-Fu was poor in execution, but its core idea of smaller characters could be made to work well on an NES. You could aim for something like Super Smash Bros.
The other way to do this, allowing big characters like in Street Fighter, is to draw one character as 8x16 pixel sprites and the other as background tiles. This allows up to 64-pixel-wide characters, or slightly smaller counting projectile attacks. But you have to design the game such that only one player faces either direction, so that you can draw the other player with mirrored sprites. That means you can't easily jump behind or roll past the other player and attack from behind unless the frames for facing the other player's back have dedicated cels. In addition, backgrounds will be plain, and you'll probably need an advanced mapper with a scanline timer to perform multiple scroll splits at the status bar, play area, and ground.
Bullet hell shooter
Limitations: Overdraw, CPU speed, 5-line OAM DMA delay
Shooters can be hard, like Recca, but they can't have more than 64 things in the air at once. Some platforms (like GBA and Neo Geo) can rewrite part of the sprite table during rendering to expand the sprite capability, but the NES can't. And even then, more than 8 on a line will flicker so bad that bullets will be invisible half the time.
Limitations: Sprite palettes; overdraw; DMC controller interaction
An NES Four Score hub provides four controller ports, one for each of the four palettes. If each player's car, uniform, etc. has the same graphics with a palette swap, there are no palettes left for anything else. If each player uses one palette, other things like projectiles and enemies would need to use the same palette as a player, or they would have to be background objects. It might be easier if the player sprites leave one color unused or if the game splits the players into two teams whose characters aren't palette swaps. Four 16x16 or 16x32 pixel sprites will also hit the 8-sprite limit with no room for projectiles unless the game is designed not to encourage them to be at the same vertical position.
Memory accesses during sample playback occasionally cause a bit from the controllers to be skipped. It takes longer to read 16 bits than 8, so not only is it twice as likely to skip a bit, but rereading becomes less practical. So either your music and sound effects during gameplay will have to be purely PSG based, or you'll have to use the signature bits to detect deletions from the bit sequence and use the previous frame's keypresses instead.
Limitations: Audio hardware, storage
A music game for the Famicom could put a digital audio player on the PCB.
In fact, the JF-13 board in the Japanese version of Bases Loaded does just this for the umpire's voiceover.
But unlike the 60-pin Famicom cart edge, the 72-pin NES cart edge lacks a pair of pins for an audio signal.
Instead it has ten pins connected directly to the NES-001 Control Deck's expansion port.
A 47 kΩ resistor from one of the cart pins to an audio pin on the expansion port enables audio out of the expansion port as on a Famicom, but Nintendo never released an accessory with this resistor, and top-loading NES-101 consoles don't even have this port.
So NES games usually have to use the 2A03's internal APU channels.
A mapper could trigger an IRQ when each sample is ready and offer a digital output on $4011 so that
dec $4011 to read a sample from the mapper and write it back to the APU, but it was determined that OAM DMA would audibly interfere with that approach.
Music games such as Beatmania, Dance Dance Revolution, and Amplitude use CD-quality audio played from a disc. Though the NES APU is capable of competent covers of popular music, as seen in D-Pad Hero, a lot of players demand the original studio recordings, or at least an electric guitar that sounds like an electric guitar and chords that don't sound warbly. Witness poor sales of the Game Boy Color versions of Beatmania and DDR.
Apart from DDR: Disney Dancing Museum for the Japanese N64 and games on Nintendo handhelds, most music games have come out on platforms with hundreds of megabytes of cheap storage: PCs and disc-based consoles. A lot of music games especially for consoles use a custom controller, shaped like a musical instrument or like the Power Pad. Because cartridges were much more expensive to replicate, bundling a controller with your game cartridge was cost prohibitive. Did anyone actually buy the Miracle Piano Teaching System?
Disc-based games using a standard controller, like Parappa the Rapper and Frequency, came on discs 600 MB to 4 GB in size and thus could afford to store the music tracks as two long samples: one for background music and one for the foreground instrument. (This pattern is called "keysound" in the Beatmania simulation community.) Guitar Hero On Tour games for Nintendo DS are 128 MB each after several Moore's law doublings of ROM density. But with a typical NES ROM size of 128 to 512 KiB, a big fat sample like that is out of the question.