"What's this? Green clothes... White fairy... Sir, could you, by chance, be a forest fairy? Oh my!" - Tingle (in Majora's Mask)
Tingle is one of the most comical characters in Zelda universe.
He's a little guy, in all green clothing, with a pointed hat, and a clock hanging on his neck. He has large pointed ears and big red nose. He's 35 years old.
"Tingle, Tingle! Kooloo-Limpah! ...These are the magic words that Tingle created himself. Don't steal them!" - Tingle (in Majora's Mask)
His first major appearance was in Majora's Mask, where he can be ocassionally found floating around with a red balloon - he does this because it obviously helps greatly with his primary hobby, which is making maps. You can shoot him down and he'll sell you his maps. In all of the games, he's referring to Link as "Mr. Fairy" - Tingle's father, who lives on the swamp, is rather embarassed that Tingle is still looking for fairies at that age.
In Wind Waker, Tingle can be first found in prison in Windfall Island. After Link frees him, he can be found on Tingle Island - and, if you have Game Boy Advance and a link cable, you can summon him with Tingle Tuner to get hints and help. When talked to on his island, he's crucial to your quest due to his ability to decipher these mysterious treasure charts. (He charges 396 rupees. Precisely.) Also, in this game you meet more Tingle's friends and relatives - Ankle, David Jr. and Knuckle - who look just about the same, only in different colors.
He also appears in The Legend of Zelda: Oracle of Seasons, The Legend of Zelda: Oracle of Ages and The Legend of Zelda: Four Sword Adventures - I don't know what role does he fill there, but I guess it's got something to do with maps again. And Kooloo-Limpah.
He also has a minor role in Super Smash Bros. Melee in Termina Great Bay arena - he's ballooning again, you can even jump on top of the balloon, and you can shoot him down with proper attacks - but you obviously don't have time to talk to him in this game.
Zelda Encyclopedia[1] notes that the Japanese name of the character is "Chinkuru".
[1] http://www.zeldalegends.net/encyclopedia/Tingle/