The following is more or less what I'm going to tell you, but in a less comprehensive fashion. So if these 2 main points make enough sense to you, then you can decide if you need more information and explanation:
- You only need to set DPI when working with actual dimensions like inches or centimeters, etc. So if you're working directly in pixels already, it's useless.
- Therefore, the only time you really need to set the DPI is when you need to ensure good print quality, and know your real world dimensions but not the exact pixels. The program will set the pixels for you based off the other information.
First and foremost, you REALLY don't need to work in real world dimensions such as inches or centimeters unless you plan on going to print with the work, like with posters or a book. Working directly in pixels is absolutely fine and works for most circumstances, the catch is knowing how much is too much, and how little is too little. More on that later, but let me break down the two main ways of setting up your work.
SETTING UP IN PIXEL DIMENSIONS
The main way I set up my documents is directly in pixel dimensions. It's much easier, especially when most of my work is meant for web or screen display, and not print.
Anyway, if you know the dimensions you want your image to be in pixels, or have a certain aspect ratio in mind, just input the numbers. After this is where people trip up and cause themselves the most problems.
They often ask, "What DPI should I set it to now?" Or they say things like, "My image is 3000x4000 pixels at 300 DPI."
The catch is, if you're working in pixels already, then setting the DPI literally changes NOTHING about your document. It doesn't make it higher resolution, doesn't make it look better in any way. Just leave it alone, you could set it to 1 if you wanted and it wouldn't matter.
In fact, try changing the number and you'll see that your document size at the lower right will not change at all. There is, however, a use for setting the DPI properly, which I'll talk about at the end, but it doesn't affect the quality of your document, which is what most people are concerned with.
SETTING UP IN REAL WORLD DIMENSIONS
If you know your work is going to be printed, you need to set up the document with real world dimensions. After you decide the size of the image, you need to set the DPI (actually in Photoshop it's PPI, but the difference doesn't matter and the term is often used interchangeably). It should be under Resolution and has units of "pixels/inch".
When working in print, usually the minimum resolution you want is 300 DPI. That ensures good quality, crisp printing at the intended size. The downfall is that often a 300 DPI image is going to have very large pixel dimensions and may slow your computer. To combat this, you can often get away with 150-200 DPI for print and still be fine.
One big thing you need to know is that when you set your work up in inches, and designate a DPI, it AUTOMATICALLY sets your pixel dimensions for you. It can be very useful for this reason, but it can also result in people working in either too small of a resolution or waaaay too much.
HOW LARGE TO WORK
I mentioned earlier about knowing how large to work in pixel dimensions so you know you're at a good resolution to get lots of detail. A solid gauge to go by is to have the smallest dimension on your painting, whether it be width or height, be no less than 3,000-4,000 pixels or so. That might be overkill for some, but it's what years of experience have shown me is a safe zone. You can certainly go higher if you want, but keep in mind that making something super high resolution will often result in people feeling the need to add more detail than necessary, making their job harder and more time consuming.
The main exception to this if you're working for film and doing something like matte painting. In that case, it's standard to work double film resolution (or higher) and set your canvas to 4,096 pixels wide, and just let the height be whatever the aspect ratio dictates, which is okay if it's less than 3,000.
I also mentioned when it can be beneficial to input the DPI even when you're working in pixels. This is for when you might want to know what the real world dimensions of your work are at a specific DPI.
In this example I set a canvas to 900x1200 pixels, then set that to 300 DPI. You can see that by going to Image > Image Size that it tells me that at 3x4 inches, this image would be at 300 DPI, standard print quality, something I wouldn't have known without doing the math myself.
Anyway, I hope this has been helpful in clearing up any confusion and saving people some headaches in the future. Good luck!