1

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?

1 Answer 1

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.

3
  • thanks - not quite following what you mean by having 'indices on those fields'
    – GWed
    Feb 18, 2013 at 15:31
  • 1
    Database index? A field can have an index or it can have no index. An index is (roughly) some extra information that helps querying faster. All fields that are relevant for your searches should have one. The ID fields obviously, but the same for say the name field of a client if ever you want to search for clients by name. Wikipedia: Database Index Feb 18, 2013 at 15:34
  • Thanks, that's helpful as I do actually search by username.
    – GWed
    Feb 18, 2013 at 15:36

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.