Database data structures for RESTful api

Question

I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources.

Initially, I though a table per resource would be a good way to go, but I'm now worried that this will result in exponentially bigger tables the further down the resource chain you go.

For example, imagine I have three resources - users, clients, sales. Users are subscribers to my api, clients are the users customers, and sales are purchases made by each client to the users account.

A sale resource is accessed as follows

GET /users/{userID}/clients/{clientID}/sales/{salesID}

So if there are 10 users, each with 10 customers, and for each customer there are 10 sales, the table size gets larger the further down the resource chain we go.

Im fairly confident that SQL can cope with large tables, but I'm not sure how read and writes will slow things down. The example above maybe doesn't illustrate it, but my api will have progressively more writes and reads the further down the resource chain we go. I therefore have the scenario where the biggest tables in my database, will be read and written to more times than smaller tables.

It will also be necessary to join tables before running queries. The reason is that I allow each user to have a client with the same name. To avoid getting the wrong client data, the users table and clients tables are joined by {userID}. This is also the case for sales. Will joining large tables and running reads and writes slow things down further?

Answer 1

I don't see exactly where this would be different without REST. This is a rather normal task for a database and queries of this kind are done all the time. Main thing to do is having indices on the id fields of course.

Once this runs for some time you will have many sales and need to query for sales not only by user/client, but limit to a certain date range (like last month). Again that should be no problem, assuming you have indices on those fields.

One weak point in your design (though may depend on your use case) is a single client could work only with one specific user. I would think that some of those users are in a holiday once in a while and clients may purchase from different users for this (or other) reasons. So maybe the sales table should have userID and clientID.