While OP poses his question in these terms...
What then is the significance of Noah's name meaning "rest" or "comfort" and how does it relate to the flood narrative?
...I will adjust that slightly and work with this (alterations indicated in square brackets):
What then is the [meaning] of Noah's name [...] and [...] does it relate to the flood narrative?
(1) What is the meaning of Noah's name?
While it is natural to associate Noah's name primarily with the Flood Story (Genesis 6-9), this is not the first place to look for the meaning of Noah's name. That comes at the end of Genesis 5:
28 Lamech lived one hundred and eighty-two years, and became the father of a son. 29 Now he called his name noaḥ, saying, “This one will yənaḥămēnû from our work and from the toil of our hands arising from the ground which the Lord has cursed.”
There are a couple oddities (at least!) here which have long been noted:
- there is a mismatch between the name, noaḥ, derived from the root NWḤ (for words meaning "rest"), and the "etymology" associated with it, using the root NḤM—although many English versions will provide "give us rest" for yənaḥămēnû, it properly means (here) "bring comfort";
- the bit of commentary associated from Lamech seems to connect most closely with Noah the farmer, in the strange story which follows the flood narrative in Genesis 9:20-29, rather than the flood story.
Regarding #1 (on #2, see below), the mismatch between the name and its gloss by Lamech has long been felt. This could possibly go back to the time of the Septuagint translation, because where the Hebrew has yənaḥămēnû, which typically would be translated by something like "comfort" (in around ~50 cases of the piel for this verb), LXX gives διαναπαύσει, "he will give rest". And this would be the expected translation if the Hebrew had a verb using NWḤ here: the equivalence of NWḤ : *παύω "rest, cease" occurs well over 40x. But this isn't the case for 5:29 — it uses the verb NḤM "comfort" (among other meanings), for which the regular LXX equivalent (>40x) is παρακαλέω, which includes among its meanings "comfort, encourage".
So how to understand this odd explanation of the name in 5:29? The rabbis recognized the problem, and had a number of suggestions, as recorded in Genesis Rabbah XXV.2, kicking off with Rabbi Yohanan:
The name does not correspond to the interpretation [given to it], nor does the interpretation correspond to the name. The text should either have stated, "This same shall give us rest," or, "And he called his name Nachman,...".
So it is clear to the that the meaning of the name has to do with rest, even if Lamech's explanation doesn't — and how they dealt with that conundrum is discussed below.
Franz Delitzsch seems to have changed his mind about whether there was a "linguistic" connection here or not. In the first English edition of his Genesis commentary (1885), he understood Lamech to be explaining Noah's significance, implying that there was no linguistic connection to be expected. He had a significant development in his thought, though, and in the second English edition (1889) he offered a more involved account, appealing to synonyms and suggesting both "phonetic groups" (NWḤ and NḤM) were "both imitative of the sound of breathing again". Not Delitzsch at his finest, I think.
Wenham's explanation has more explanatory power: "As is often the case in the OT, the etymology is not scientific; it is simply associated with the name because of its similar sound." (G. Wenham, Genesis 1-15 [Word, 1987], p. 128.)
Not that Wenham's explanation is convincing here, either. But this does illustrate how the tension in 5:29 has been felt by commentators, and it suffices here to say that the meaning of the name "Noah" is "rest", not "comfort", and that the notion of "comfort" comes from Lamech's explanation of who his son (Noah) would be — but he does not provide a meaning for his son's name.
Does Noah's name relate to the Flood narrative?
Well ... maybe. And at one level, certainly. Most commentators who attend to the Hebrew text point out the several wordplays arising from the N-Ḥ combination, like:
- the LORD was sorry [wayyinnaḥem] he made man (6:6);
- Noah [noaḥ] finds favour [ḥēn] in God's eyes (6:8);
- the ark came to rest [wattānaḥ] (8:4);
- the dove found no resting place [manôaḥ] for her "foot" (8:9);
- the LORD smelled the pleasing aroma [hannîḥōaḥ] of the sacrifice (8:21).
(That list drawn from V. Hamilton, The Book of Genesis, Chapters 1 - 17 [NICOT; Eerdmans, 1995], p. 258-9 n. 30.)
So at that level, yes, it certainly seems like Noah's name is related to the flood story in this series of puns/wordplays.
But that isn't the meaning of the name (once again!). Here, however, there have been suggestions, too, starting with the rabbis (see Genesis Rabbah):
- R. Yoḥanan argued that the rebellious creation submitted with the arrival of Noah;
- Resh Lakish argued that with Noah the unruly waters finally had "rest";
- R. Leazar argued that the name had to do with the savour of the sacrifice (8:21);
- R. Jose b. Hanina argued that it had to do with the resting of the Ark (8:4);
- in later times, Rashi also pointed to the farming innovations associated with Noah (along with some other pertinent comment), so not to do with the flood.
It is striking the way in which a connection to farming is pretty much as prominent in these explanations as one connected to the flood.
The puzzle is by no means settled. In recent years, there has been a great deal of attention to the ANE parallels flood accounts, with the variety of names they use for the flood hero and the account of the flooding itself, with many associations with "rest" to be found. Details conveniently found in:
The meaning of Noah's name is clear: it means "rest". But its relation to the flood story remains somewhat obscure: while there are many connections betwee the name and the telling of the story in Genesis 6-9 (inclusive of the concluding "farming" episode), picking out any one of those as providing the "correct" association is an uncertain exercise. Stéphanie Anthonioz summarizes the traditional options (p. 188):
- the deluge and its waters that immerse the earth preventing it from offering some rest;
- the cause of the deluge in divine regret,
- and finally, the consequence of the deluge, since only (with his own) saved from a destroyed humanity, Noah not only consoles God, but consoles himself in wine and, one might add, finds his rest there!