I know about the two modes of working in Pi using python, Board and BCN. However, I am really confused as to the instances or situations where I should use them.
An example demonstrating their use would really be helpful.
There are at least three incompatible GPIO numbering systems used on the Pi.
Most GPIO libraries will support one or two numbering schemes.
It does not matter which numbering scheme you use as long as it is supported by the software you are using.
I would recommend using Broadcom numbering as it is the one most widely used.
For examples see https://elinux.org/RPi_GPIO_Code_Samples
For reference the popular Python GPIO libraies are as follows.