I'm aware good songwriting comes through endless hours of practice, listening and breaking down other songs and compositions and that there's no strict rule as to what can be sung over what chord as long as it sounds good to the composer but I do want to cement my learning as to what makes an objectively good melody. Without wanting to get into complex territory. For starters I do know that if I were to write a melody in Cmaj I can use any type of triads, 6th, 7th or 9th chords and all of its inversions as long as they're within that scale but does the melodic line have to strictly follow the notes within the chord progression for it to be considered good? I'm assuming if I were to sing a B note over a CEG chord which makes it a C7 less consonant than singing a G note therefore I shouldn't hold the B note for long so I'd have to move the melodic line towards the chord to create consonance. My question is do objectively good melodic lines frequent in and out of the field of consonance and dissonance to create tension in melodies?
THERE ARE NO RULES! You must stop trying to "learn" by listening to other people tell you how it is done. Most of the time these people suck and have no clue themselves and will never amount to anything nor write good music.
How many great musicians are also great theorist? Virtually none. While there are patterns that people use that create a certain style, and if you are going to create in that style you have to use those patterns(else it won't feel/sound the same and hence how the hell is the listener suppose to know you meant that style?)
If you want to create music like Bach, listen to Bach(Or insert your favorite composer here) and study his patterns. You might find that reading some "theory" books will help but must just obfuscate what is actually going on(someone comes up with "theory" to try to explain it and end up digging a whole because they don't understand themselves and then they preach it to other people and make those people more confused about what is going on... then those people write theory books, etc...).
If you want to write music like The Eagles, then study their music.
Just remember, you will never sound like them, and chances are never amount to much.
Until you realize that music is an expression of your soul and learn to tap in to that, your music will always sound contrived or simply a copy cat(which can still make you a living and sometimes you can do pretty well at it, but your music will never last).
My point is simply this: YOU MUST GET OUT THE RULE BASIC MUSICAL CREATION FROM YOUR HEAD!!!!! Once you do that and you tap in to the real thing that creates music, you will create music and people will respond to it. If you create music by rules, it will sound like rule basic music(which, is still music but it, at least so far, never sounds good).
The reason is simple: COMPLEXITY! Music is so extremely complex that one cannot codify a set of rules and put it in the spoken or written word. What happens is exactly what you have described. You get generic rules that basically leave your music bland!
A B works great over a C chord if that B note is used musically... what does that mean? It means you have to use your soul to make that B work. A B also sounds terrible over a C chord. So which is it?
Music theory is two things: 1. What composers generally did(like 50-75% of the time) 2. A language to communicate musical ideas(not the best language, just a language).
Learning the musical language is the most important thing you can get from outside as it lets you communicate your ideas in a more concrete way with yourself and with others. It helps speed the process along but is not necessary.
Learning what composers generally did helps you imitate them. The only real way to do this is to listen to them. How are you suppose to imitate Bach if you never heard him? You can have a book full of rules about how Bach composed and it will help a little but you won't sound like Bach. Now, maybe if you spend the next 10 years studying these rules it will get you closer than if you only studied 1 year, but there is an easier way!!! ->
Stop trying to sound like Bach! Sound like yourself and spend the next 10 years developing your own style. Stop trying get recognition by showing how good you can imitate...
OK: The point of all that was to try to counterbalance your need for rules. Rules are like training wheels. You need to only use them to keep you from falling over. Once you get a sense of balance you need to take them off or you just look stupid(and can go very fast).
If you want to understand what makes good melodies you need to learn to improvise. By improvising a lot you develop the a sense of what works and what doesn't. It is impossible to explain because it it works on a different level than anything most people are used to(which is why many people find it so difficult).
See, what makes a note work is not the note itself but what comes before and after. A B note is just a B note... over a C chord it is a maj7 and over a B chord it is a root. But it can sound good or bad over either.
You are right about tension and that is effectively the best word to describe it. As you improvise you learn to control the tension... and when you understand this then you realize what I'm saying is more or less true. A B note over a C chord can increase the tension and if you need tension, which depends on context, then it will work.
The reason why "rules" don't work is no one has figured out how to measure this "tension". Because it depends not only on the actual sonic characteristics but also on many non-musical things. Because our brains are so much better at subconsciously figuring this stuff out it is best to learn by immersion rather than rules as the rules end up blocking one's ability to sense this tension/release that happens in music.
An analog is working out: You can watch someone work out and listen to them tell you how it "feels" or have someone that watches someone work out tell you(obviously the first case is better) or you can work out and feel it yourself. At first, sure, it won't feel right and you will "suck" but with time you will get bigger and stronger... cause that is what happens to everyone that works out(properly and long enough of course).
For some reason, we has humans have some need to make "sense" of reality(be it music, painting, quantities(math), etc). But in the arts, this need generally slows down the actual process of absorbing and releasing what is actually going on because our conscious thinking brain is much much slower than our subconscious. It seems to make a lot of people feel "secure"/"superior"/etc when they think they understand something on the conscious level but this generally is exactly the opposite that one wants with music because good music comes from "within"(the massive complex of all that you are...).
The fact that you need some "reason"(which is a justification for yourself) of why one melody is better than another proves this.
Do objectively good melodic lines frequent in and out of the field of consonance and dissonance to create tension in melodies?
A few comments. 'Objectively' is going to get you in trouble here. It's impossible, in my opinion, to define any objective standard for good music. You can measure popularity, but I think most people would agree that modern pop music is not all objectively good. Longevity might be another option, but there's plenty of terrible old music as well. So I think you're going to find it impossible to define good music objectively.
That being said, creating and releasing tension is good way to think about melody and harmony, particularly when they act together. Tension is not only harmonic, by the way. I can build rhythmic tension easily - a drum fill into a chorus, for example. The fill builds tension, the return of the beat releases it.
You also have to remember that consonance and dissonance are cultural. There have been attempts to scientifically measure them, but I don't think they have had great success.
So, this is a hard question to answer well. Which is probably why you've got so many answers. I actually think your question has a reasonable premise, hidden under a few weak assumptions.
As to your example of using a major seventh, it depends on, well, everything. The style, the mood you are trying to evoke, the preferences of your listeners. How dissonant is a major seventh, exactly? See how hard it is to be objective? This is the artistic part, and you get to make the decisions. So make a few, find out what does or doesn't work, and do it again. And again. And again. You'll get there. Just beware of overtheoreticising everything. It will almost certainly get in the way of the actual music, which is the point.
There are some statistical things you could look for melodic composition. This 2013 paper used a corpus methodology to analyze chord progression and melody in Rolling Stone's "500 Greatest Songs" list. Read the whole thing, it's quite an interesting paper.
There are some other things, like "great melodies have one highest note approximately two-thirds through a phrase" or something to that effect.
is there a book I could possibly pick up
One book which I believe may suit your needs is called The Complete Idiot's Guide to Music Composition by Michael Miller. It's got a chapter or two on melody. Perhaps it won't go as deeply into melody as you want, but I think it is a decent overall resource. And, it's cheap.
Short answer: No. Medium answer: not necessarily. Longer answer: your question implies that good melody writing is intrinsically linked to harmony. I would argue that the attributes of good melody writing have nothing to do with harmony, for the most part. For example, many good melodies are taken from their original context and placed into new arrangements, often with new harmonies, but the melody is still good. In fact, this is the basis of the theme and variations. In conclusion, weaving in and out of consonance and dissonance is a matter of the arrangement of the melody. Composing a good melody is another question.