Super NES CD-ROM
(Thanks to NecroVMX's video series Urban Legends Of Video Gaming for clearing this up.)
Back in 1988, Nintendo inked a deal with Sony to produce a disc-based console tentatively titled the "Play Station" (two words). Sony and Philips jointly created the CD-ROM format, which boasted greater capabilities and (at the time) better anti-piracy measures than floppies. (Sony also designed the SPC700 sound chip which is found in the SNES.)
A Play Station console was shown at trade shows in 1991, and while it was originally envisioned as a way to play CDs on the SNES, a deal was struck in 1992 to have Sony's console sport a slot for SNES cartridges (with Nintendo keeping full ownership and most of the profits from said carts).
Everything quickly fell apart when the then-president of Nintendo realized the contract's wording let Sony have full ownership and profits over the console's games. The company terminated the contract and forged a partnership with Philips -- while Sony rebuilt the project from scratch, dropping the cartridge slot and creating the CD-ROM-only PlayStation (now one word).
Nintendo later terminated its contract with Philips, and the latter company created the CDi -- which featured three games based on The Legend of Zelda franchise and one based on the Super Mario Bros. franchise (another two were planned, one based on Super Mario World and another called Mario Takes America, but didn't get very far). These games all sucked, and are best left unmentioned in discussions about their parent franchises.
During this situation, Squaresoft -- known these days as Square Enix -- was becoming increasingly frustrated with Nintendo's draconian censorship policies, publishing restrictions, and refusal to move away from cartridge media (which, at the time, had far less storage space than CD-ROMs). Squaresoft -- then Nintendo's most popular third-party development studio -- eventually signed a contract with Sony, which eventually published the PlayStation's Killer App: Final Fantasy VII.
In other words, Nintendo indirectly created one of its greatest rivals.
Nintendo's next system, the Nintendo 64, was the only cartridge-based system of its era. Nintendo's decision to stick with cartridges when other systems had moved on to a CD-based format was boneheaded, but this isn't the place to discuss that. Nintendo's systems after the N64 use discs -- but the Game Cube's discs were half-size (80mm) and weren't quite mini-DVD, while the Wii discs aren't quite DVD either, and the Wii U is said to use a proprietary format which isn't Blu-Ray.
As an aside: the company who was really scared of all this was Atari, whose Jaguar console wasn't doing too well against the SNES and Sega Genesis (despite apparently being the technologically superior system). Then-CEO Sam Tramiel began idiotically boasting about how the Jaguar was better than both the PlayStation the Sega Saturn, both of which hadn't been released at the time. Tramiel also threatened to take Sony to court if it sold the PlayStation for less than $500; Sony did -- $300 -- and Tramiel didn't. Atari's foray into the CD format (a CD add-on for the Jaguar) didn't help matters, as it featured no good games and was really badly designed.
As another aside: An enterprising emulator developer, byuu, took it upon themselves to create a "What If" situation as to what this CD-ROM addon could have been capable of. To that end, the MSU-1 [dead link] enhancement chip was created with media streaming capabilities and a storage capacity of 4GBytes, well above any official game ever released. There is currently one game that uses it: Super Road Blaster, a homebrew SNES port of the Sega CD game Road Blaster (size 512MBytes). If nothing else, it's worth watching a true CD-quality game running on an actual SNES (albeit with a modern flashcart with a built-in MSU-1).