11

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?

Improve this question

3 Answers 3

Reset to default
31

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.

Improve this answer
2
  • thanks. So what you're basically saying is that so long as i normalise my databases and setup appropriate indexing etc, there should be no performance problems for what i want to achieve
    – GWed
    Feb 18, 2013 at 18:16
  • 3
    Yes. Nothing you've mentioned suggests deviating from a normalised schema would be necessary.
    – Mark Storey-Smith
    Feb 18, 2013 at 18:26
9

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.

Improve this answer
4
  • thanks- Yeah i do need to read up further. My projects to date have had very little database work. I will read the resource you recommended
    – GWed
    Feb 18, 2013 at 16:05
  • I think shared-shared is the way to go for me. My 'users' are not 'tenants'. They do not require isolated data and will never need direct access to any database management functions. From what I read, this would suggest shared-shared is best? What do you think?
    – GWed
    Feb 18, 2013 at 18:17
  • 2
    shared is the easiest. You must add the user_id as a key to every table and add left.user_id = right.user_id to relevant joins (usually, all). Some ORMs support scopping the access. And don't be fooled, your users are tenants, since you don't want user 'foo' to see/modify the sales of user 'bar'.
    – Remus Rusanu
    Feb 18, 2013 at 18:35
  • Thanks. Im beginning to see that indexes are key to creating my database structure.
    – GWed
    Feb 18, 2013 at 19:10
0

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...

Improve this answer
2
  • 1
    some links or further explanation would be helpful
    – GWed
    Feb 19, 2013 at 10:04
  • Sorry, can't comment yet..
    – Matt Koskela
    Feb 19, 2013 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.