Design a WebService API to a Database Schema

Suppose I have the following tables in my database (which I can redesign at will if I want to):

  • user: info about users of the system each user has a set of authorization.
  • authorization: info about what each authorization means. each authorization is associated with a number of hosts it applies to.
  • host: info about hosts involved in the application. each host has a location.
  • areas: groups locations together.
  • location: group hosts together.

I want to design a webservice interface to this database schemas so that clients of this webservice will be able to ask questions such as:

  • what are the users of this system?
  • what authorizations has user X?
  • in what location is host Y?
  • what are the hosts located in location Z? -. .... and so on.

What should the functions/methods of this interface be?

  • One popular solution is to have:
    • getuser(id)/getusers() get users, along with the list of ids of authorizations each user have.
    • getauth(id)/getauths() get all authorizations along with the list of ids of hosts they apply to.
    • gethost(id)/gethosts() get all the hosts. along with the id of the location of each host. .....

With this scheme, getting the location of a hosts means doing two calls to the api:

   h = gethost(id),
   loc_id = h.location_id; 
   location = getlocation(loc_id);

What I don't like about this approach is that it forces the client of the api to do a lot of (asynchronous) calls to the API tracing through the hierarchy of objects before getting the info he wants.

In your experience is this the best way to do this?

PS. If your solution involves changing the schema, I'm all ears too.

EDIT: In other words, what I want is good middle ground between

  • Solution 1: Web service has only one method: query(String) which accepts a query in some db query language like SQL.

  • Solution 2: Web service has one method for every possible query that users might want: getUserWithAuthorization(user_id) getUser(user_id) getLocationOfHost(host_id) getAreasWithLocations(), ... etc. #interfacebloatinghell.

Answers


First of all, drop all verbs (getResourceX).

Usually you can design the API in the way that a client MAY do a single call and getting all the information but also get data on a more granular level.

If I assume correctly your set of data in order from largest to smallest subsets is like this: Areas -> Locations -> Hosts -> Authorizations -> Users

So this can be your resource URI

1 get call to

api/areas 

will return all Areas along with all their Locations and for each Location their hosts etc.

If you want information about a particular Area you can do 1 call to

/api/areas/{id}

Need Your Help

Laravel and Charting - Determine The Difference Between Datatimes

php charts highcharts laravel google-visualization

I've been trying to create a chart similar to http://bit.ly/1jF6AY7 for a few weeks now using the Highcharts library. However, it does not understand that there should be zero values (if no value /...