wrote: I don’t really understand how the photo library on the internal drive of my MacBook relates to what I see in the Photos app on my iPhone and iPad, and what is in iCloud. Are you able to explain that please?
Using iCloud Photos ensures that all the pictures in iCloud.com and in all the Photos Libraries of all the connected devices are exactly the same. If you take a picture on your connected iPhone, it will be copied to iCloud.com and then copied to all the devices. If you import a picture from a friend's text into Photos on a Mac, then that will be copied to iCloud.com and then to all the devices. If you delete a picture on your iPad, then it will be deleted at iCloud.com and then deleted from all your devices. All the pictures in iCloud.com and in all the Photos Libraries of all the connected devices will be made to be exactly the same.
That's why you can't think of iCloud as a simple backup-- if you mistakenly delete a picture from one device, it's erased everywhere. That's also why you get 30 days of Recently Deleted to change your mind.
… What issues are involved in setting up a new user?
Another user gets its own set of folders like Music, Pictures, Documents, Desktop, etc, that are completely independent of the the others. One user doesn't have access to the Photos Library, for instance, of another user. Anything in a folder in my user's folder,
Macintosh HD/Users/rtaylor/Pictures/Photos Library.photoslibrary
is visible only to me, and stuff in another user's folder in not accessible to me.
However, if I have files that I want to be available to all users, I can put them in
Macintosh HD/Users/rtaylor/Public/
and others can see them. Or I could make a new folder, that I might call Everyone, in the Macintosh folder,
Macintosh HD/Everyone/
and, since it's not in a particular user's folder, it would be available to everyone.
A Photos Library will work in the Public folder or in the Everyone folder. (But it will not work in the iCloud Drive folder.)
You can see some detail about setup here:
Add a user or group on Mac - Apple Support
I don’t have another photo library so would need to set one up. Would that then replace the old one, and if so, how would I get my old one back?
Macs (not iPhones or iPads) can have multiple Libraries. You can just double click on the Library you want and that's the one that will open in Photos. And, of course, other users can have their own Photos Libraries.
Only one Library can be designated the System Library, and that's the only Library that can connect to iCloud. This is done in Photos>Settings>General.

My "Use as System Photos Library" is grayed out, because it already is.
I think I’ve looked at rebuilding my photo library. If I remember correctly it said it would delete all my photos from the device before rebuilding it. Is that correct?
No. But it is absolutely advisable to have a backup copy of your Photos Library on an external drive just in case something crazy happens. That should be the first thing you do before anything else. Pictures are precious! I have two Time Machine backups and another Drive just for copies of my Photos Libraries!