Let $X$ a compact orientable manifold of dimension $2n$ and $Y$ a compact submanifold of dimension $n$. Further let $U$ a tubular neighbourhood of $Y$. When I did some calculations I got the conjecture:
$Y$ has self-intersection $\pm 1$ $\Leftrightarrow$ $H^n(U,\partial U;\mathbb R)\simeq H^{n}(U;\mathbb R)$
Is this really true?
It seems logical that there is some connection between both sides because the information about how $Y$ is embedded in $X$ is encoded in $\partial U$.
In addition to what you said above $Y$ should be connected (for convenience) and oriented (by necessity). This is all about the Euler class and the Gysin sequence.
You're effectively asking whether the maps $H^n(U) \to H^n(\partial U)$ and $H^{n-1}(U) \to H^{n-1}(\partial U)$ are zero and surjective, respectively.
Because $U$ is the total space of a vector bundle over $Y$, we may as well replace $U$ with $Y$. The maps above are the pullbacks induced by the projection $\pi: \partial U \to Y$; note that this is a sphere bundle over $Y$.
In particular, these maps fit into the Gysin sequence
$$\cdots \to H^j(\partial U) \to H^{j-n+1}(Y) \xrightarrow{e \smile } H^{j+1}(Y) \xrightarrow{\pi^*} H^{j+1}(\partial U) \to H^{j-n+2}(Y) \to \cdots .$$
The class $e \in H^{n}(Y;\Bbb Z)$ is called the Euler class, and following the isomorphism $H^n(Y;\Bbb Z) \cong \Bbb Z$ coming from the orientation of $Y$, agrees with the self-intersection number of $Y$. (This follows quickly from the definition of Euler class, which would be too long a discussion to be appropriate for this post.) In particular, it is nonzero in real cohomology if and only if the self-intersection number is nonzero.
First set $j = n-2$. We want the map $H^{n-1}(\partial U) \to H^0(Y)$ to be zero so that the map $H^{n-1}(Y) \to H^{n-1}(\partial U)$ is surjective. Equivalently, we want the map $H^0(Y) \xrightarrow{e \smile} H^n(Y)$ given by the cup product with Euler class to be injective. This map is is either an isomorphism or zero (depending on whether or not the self-intersection number is zero). In particular, if $e$ is nonzero, then indeed $H^{n-1}(Y) \to H^{n-1}(\partial U)$ is surjective.
Now set $j = n-1$. Then again the map $H^0(Y) \to H^n(Y)$ given by the cup product with Euler class is either an isomorphism or zero, and so if $e$ is nontrivial, the map $H^n(Y) \to H^n(\partial U)$ is zero.
Therefore if $e$ is nontrivial (which is true if and only if $Y$ has nonzero self-intersection), the map $H^n(U, \partial U) \to H^n(U)$ is an isomorphism. This is true more generally than just self-intersection $\pm 1$.
Conversely, the map $\pi^*: H^n(Y) \to H^n(\partial U)$ is injective if $e$ is zero, in which case the natural map $H^n(U, \partial U) \to H^n(U)$ is zero.