Since you asked for a general answer, and not one specific to your example, I'll put in my two cents:
(You didn't tag a system, and I don't know what "TDE" is, so I answered this medieval D&D-style.)
The Invention Process
Human ingenuity is unbounded, especially when it comes to new ways to kill people. The problem is that we as residents of the twenty-first century have a much greater knowledge of physics and math than most people in the medieval world. People knew how to use battering rams and slings, but not about Newton's Law that explained them. Archers could predict the trajectory of an arrow, but nobody could do the math to explain it. If a player can relate their idea to a well-known item in their current setting, let them do it. If what they're trying to replicate hasn't been invented yet, still let them do it, but have it require maybe a DC 15 Int check and/or 1d4 months of research. (In your example, it's pretty easy to look at a sling and think "stones moving fast hurt." It's harder to come up with the idea of directing the stones via a pipe - that might take a check or some trial-and-error "research".)
Using the Invention
Generally, MacGyver-ing weapons wouldn't require any sort of skill check, but the resulting weapon will almost always be improvised or exotic and take a penalty to its use. Maybe let the character gain proficiency with it, but only after a long time training. The exception is siege engines, which already have a Profession skill associated with it. Otherwise, require Craft checks—either as generic Int checks or in some specific associate skill—to successfully operate. I wouldn't create a whole new skill just for a player-devised invention. If anything, create a new Craft or Profession subskill.
Balancing Player Inventions
Balancing player inventions is even harder and more subjective. Note, though, that unbalanced things tend to tip over. If your 1st level PC figures out how to make a shotgun that destroys everything you throw at it, blow it up in his face. Don't be malicious about it, but make it clear that the character simply isn't skilled enough to use the powerful item reliably. Maybe in a few levels, if the character has invested a little more time into it, a better system can be developed. "But for now," say, "you can tell this particular invention has a high chance of failing catastrophically." In your example, maybe require a Dex check not to touch the staff to the pipe itself, which would throw the pipe and probably injure the character's arm (Dex or Str damage.)
Another good way to balance player inventions is to simply make them impractical. If the player isn't putting a lot of thought into actually developing something new, but simply says "wouldn't this be cool?", it's probably impractical already. In your example, it would probably be a full-round action to load the stones into the pipe, and another standard action to insert the staff into the pipe, firing it off. Let them do it, but they'll quickly learn that the combination of terrible rate of fire, poor range, and unreliability simply isn't worth it.
If, however, the player's putting a lot of thought into it, and their character actually invests time and money into it's development, totally let them do it! Maybe delay their research til it's a little more level-appropriate, but let them know it's feasible, just not right now. Balancing this is a little harder, and basically comes down to rewarding role-play. One trick I found is to make sure that the invention benefits the party, and not the character. If the entire party becomes slightly unbalanced, you can just up the encounters a little bit, and everyone has fun. If it's a character that's unbalanced, they take center stage and everyone else feels left out.
Once, I convinced my GM to let my high-level Gnome Wizard invent a telepathic network by creating a massive intelligent item which could scry and cast telepathic bond. (Basically Skynet.) I got government funding for it and developed it in my and my character's spare time, (our campaign had several skips of multiple years between sessions,) and they used it to communicate with their army's general and special task forces (including our party.) The GM balanced it by saying that my character couldn't become rich off of it—all profit was reinvested in what became a medieval CIA—and that it benefited the whole party. Plus it made a great plot point when Skynet decided it knew better than the government...