Question: As above, what is the reason for needing a memory module on a RAID card?

The card I am looking at (HP P410) comes with either 256MB or 512MB but unsure which to get and why.

Answer: 
The memory is used for read and write cache which improves the performance of the storage. The basic rule when it comes to cache is buy as much as you can afford. The more cache you have the better the performance of the disks will be as data can be written to the memory if the disks aren't in the correct position.

You'll also want to make sure that the RAID card has a battery built on to protect the data in the write cache if there is a power outage.
Aside from the read/write cache, without the memory, some RAID controllers disable some functions like array expansion. It's definitely better to have it, than to not.