I thought about that, but (from my limited knowledge of Modding) I think that new workshops can be done, but not new areas... Or indeed new things to be done within existing area-effect places, like dining rooms[1]. The best I can think about is making something like snuff or chewing tobacca that's equivalent to food. But nothing that would involve a pipe.
So, anyway, if you search (and I've not gone into the threads, myself, but I know they exist, it may even be on the Perpetual Suggestions Thread) you'll see the existing thoughts. And, one way or another, it would need Toady to code it in. Not sure if he's utterly anti-smoking (or just not bothered about it, within his universe that he's creating), but he hasn't yet, and there's enough fictional background to suggest that some dwarves might like smoking (even if they've just picked the habit up off some random halfling civilisation). Indeed, to logical extent (may or may not have already been suggested) it could be assumed that the development of this feature would involve a psych-profile element in the dwarf's make-up regarding smoking their love or detestation of smoking, and so maybe an option to mark as "Smoking/non-smoking" the dining areas, for appropriate dining (dis-)pleasure.
Oh, and another point where core coding intervention is needed, and beyond mere modding, would be the blue-tinted tobacco smoke-pall, to be added to the existing water-vapour, magma-mist, miasma and normal smoke. If differentiated (as I think it should be) from the latter.
(So, I've just spent a lot of time speculating instead of searching for the same information about whether it's already been speculated about, as well as covered the "what you can already do" bits (that have probably already been covered), after saying that you maybe should have searched. There's probably a certain irony in that.)
[1] Well, they're dwarves, I doubt they'd utterly ban smoking around their eating areas, whatever various jurisdictions here on Roundworld tend to proscribe.