I'm currently working on a larger project, utilizing a microservice architecture.
The project will handle over 100 000 users, which all will be stored in a separate service. The question is what the best solution for handling relations to these users in other databases will be. We are at the moment only using relational databases (not NoSQL).
Any opinions on whether each service should have a user table holding the ID and maybe data specific to that service, or if all the data should be kept in the central user service and just adding an index on the userId column?
It depends on your business and non-functional requirements.
For example, if your downstream microservices don't need other fields from the User entity then you should not create any local tables. By "need" I mean for displaying purposes.
If you need some attributes in other microservices then you should have at least a local cache (i.e. a SQL Table) with those fields. This is needed to ensure that the system is resilient; for example, in case the Users microservice fails or is slow, then the other microservices would not be affected; they should be able to function independently. In the worst case scenario, those fields will be empty or filed with a default value, i.e. "information not yet available".
Your microservices will also be faster, as they don't need to join data from a remote source to fulfill users' requests.