I am struggling to decide on the best way to design my database tables
around my resources.
Don't.
Design your API according to RESTful principes, design your database according to normalisation principles. One does not need to impact upon the other.
Your database should not contain a SaleResource
table, it should contain a Sale
(or purchase/order) table. That table will include a primary key that uniquely identifies a Sale and foreign keys to related User and Customer tables.
You REST api will translate a request for the resource identified by GET /users/{userID}/clients/{clientID}/sales/{salesID}
to the appropriate database query, retrieve the row, construct the resource that represents a Sale and return it to the client.
Be mindful that you are currently exposing what appears to be internal database identifiers (UserID/ClientId/SalesID) to the outside world. It may be appropriate in your case but generally <entity>ID
feels off in a RESTful API.
From the comments added to the original question, I gather that the central problem with the legacy design centers around types and subtypes (often called classes and subclasses) of claims. This is a classic problem in relational design, Often, new database designers will have had some formal learning concerning many-to-many relationships, data normalization, and similar topics, but no formal learning regarding classes and subclasses.
The Info tab under the Subtypes tag has a brief summary of the problem. It mentions inheritance and object modeling in passing. I want to expand on this a little. The relational data model, in its original form, has no support for inheritance, although some DBMS products have extended the model with inheritance features.
Fortunately, the problem is well understood, and you can gain valuable insights from people who have studied the problem in depth. I particularly like Martin Fowler's treatment. He bridges the gap between how programmers think and how data architects think.
In your case, it appears from your description as though there are a bunch of claim subtype tables, but there is no generic claim table. I'm going to recommend one particular design for your evaluation. It's called "class table inheritance". And I'm going to also recommend a technique called "Shared Primary Key".
In class table inheritance, there will be one table for the generic data that applies to all claims, no matter which subclass they belong to. This table will have a claim ID, used to point to a row, like the usual table. The other columns in the table will contain the attributes that are common to claims regardless of what class they belong to.
The other tables contain data that pertain to particular classes (types) of claims. The ID column of these subclass tables deserves particular attention. It's not populated in the usual way, by having the DBMS assign a unique number to it. Instead it is an "inherited" copy of the ID column of the Class table. This column is both the primary key of the subclass table and a foreign key that references the appropriate row of the claims table.
When you want to deal with a particular class of claim, just join the claims table with the appropriate subclass table. The irrelevant claims will drop out of the join.
The reason I put "inherited" in quotes is that it requires some programming on your part to cause this inheritance to take place when a new claim is added. You have to add the row to the claim table, then obtain the automatically generated ID, then propagate it to the subclass table, before making the insert into the subclass table.
That's my recommendation, but it's your call.
Best Answer
"The Data Model Resource Book", Volume 1, has a whole chapter on dealing with contacts and solves those kinds quite extensively. As does any CRM btw.
Basically:
Sorry, the userRole approach sucks - it is widely accepted - by anyone with experience as a mistake. So, your staff NEVER EVER BUY? Nice products. Roles and entity information are separate.
There are obviously relationships between entities - a is staff of b (from time to time, btw.). This is a m:n relationship and requires a second table.
Btw., I really hope you do not plan to store the password in the password field - beginner mistake, violation if basic security principles. Never ever store a password, only a salted hash - hashed many times. Read up on that topic.