SQL Database Structure 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 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.

Answer 2

Relational databases (hence SQL) are really good at locating one (or few) rows from huge table. This is what indexes are for. They're also pretty awesome at handling joins. You don't really have a question. Between the lines, you're basically asking about how to do a multi-tenant database design. I suggest you read Multi-Tenant Data Architecture before asking further questions. Choose one of the patterns (separate database, shared database separate schemas, shared database shared schema) and then we cna discuss specifics. Right now you only envision the last pattern, but did not consider the pros and cons. Read up.

As a side note: you don't need user/{userID} in the URI. You know the tenant (userID) from the authentication info.

Answer 3

Just to add to what has already been said here - you may want to build an interface between the actual API and your database layer. It makes adding caching and summary tables easier down the line...