A mod, according to Wikipedia1):
is the alteration of the program code of a video game in order to make it operate in a manner different from its original version.
It can be anywhere from just new maps, modified weapons and the like, to completely new games that changes the way the game is played. Valve embraced modding ever since they released the Half-Life SDK. This calls for some of the prolific modding communities around. This stemmed careers and games, such as Day of Defeat, Team Fortress and Counter-Strike (all of them are acquired by Valve). Famous GoldSrc mods include They Hunger, Cry of Fear, Earth's Special Forces, Sven Coop and Sweet Half-Life as well as Paranoia. All of them have thrived from the limits and capabilities of GoldSrc for their functionality and playability.
There are two types of mods, the Serverside and the Clientside mods. Both of these terms matter most when you are now going to code your game. From Wikipedia2):
«Clientside mods add content to the game, such as a new storyline, models or sounds. In contrast, serverside mods add functionality.»
For example, your weapon mods, map mods and new models mods go to clientside mods, while new mechanics go to serverside mods. Clientside mods are very common, since only a few people edit the code for their purpose and instead resort on what's already available for their mod. They do not mean if they are single player-only or multiplayer-only mods.
Take note though that whatever mod you'll make, it's still up to you, since you're the one who will decide what type of mod you're going to make that is suitable for you.
Modding a game is as old as gaming itself. People tend to tinker with things and put a new spin to them. In modern video, several people like Tim Sweeney at Epic and John Carmack at iD made sure that their games can be tinkered by people, thereby extending the lifetime and playability of the engine. From just making map editors to giving away the source code, these people along with other software companies defined the way that games are done and shared.
Originally, companies fear that modding will jeopardize their game's sales, overshadowing their product (maybe because their products are inferior in the first place :p). However, companies like Epic, iD and Valve proved justified that modding gave the opportunity to people to make something and put their creativity to an outlet, making a community that fortifies and improves the games they play, however they want to play them, in ways where only the imagination is the limit.
Articles ~ Making Half-Life Mods - Part 1 - This is a very good article by one of the creators of the Classic Team Fortress regarding what makes a mod successful and setting up a team for it.