CODEWITHSUNDEEP
angularjshttpdatabase-design
Nick
Nick

Reputation: 307

Reducing amount of http requests by grouping queries

So I have a bunch of chatty http requests in my Angular 1 app which are bottle necking many of the other requests. Imagine I have a list of Users from a totally different data source and I make calls to 5 different tables such as: 

user.signup
+-----+------------+
| uid |    date    |
+-----+------------+
|   1 | 2016-12-13 |
|   2 | 2016-12-01 |
+-----+------------+
user.favourite_color
+-----+-------+
| uid | color |
+-----+-------+
|   1 | red   |
|   5 | blue  |
|   7 | green |
+-----+-------+
user.location
+-----+-----------+
| uid | location  |
+-----+-----------+
|   2 | uk        |
|   3 | france    |
|   9 | greenland |
+-----+-----------+

The reason they are in different tables are because the fields are optional. The way I see it I have 3 options:

Put them in 1 table

So I could just group them all in 1 table and have a bunch of null columns but that just doesn't sit right with me in terms of DB design.

+-----+------------+-----------+-------+
| uid |    date    | location  | color |
+-----+------------+-----------+-------+
|   1 | 2016-12-13 | null      | red   |
|   2 | 2016-12-01 | uk        | null  |
|   3 | null       | greenland | null  |
|   5 | null       | null      | blue  |
+-----+------------+-----------+-------+

Join them all with 1 request

So I could just have one query that joins all these tables but the way I see it they would have to be full joins with the expectation that some uid's wouldn't exist in some tables. e.g.

+------+------------+-------+-----------+-------+-------+
| uid  |    date    | l_uid | location  | c_uid | color |
+------+------------+-------+-----------+-------+-------+
| 1    | 2016-12-13 | null  | null      | 1     | red   |
| 2    | 2016-12-01 | 2     | uk        | null  | null  |
| null | null       | 3     | greenland | null  | null  |
| null | nul        | null  | null      | 5     | blue  |
+------+------------+-------+-----------+-------+-------+

which is probably even worse!

Change the way the requests are made?

Maybe make some clever changes how the requests are made:

function activate() {
    $q.all([requestSignupDate(), requestFaveColor(), requestLocation(), ....])
    .then(function (data) {
        //do a bunch of stuff with the data
    });
}

which I want to change to:

function activate() {
    requestUserData();
}

Any suggestions?

Upvotes: 2

Views: 62

Answers (2)

bhantol
bhantol

Reputation: 9616

This is a typical ORM problem - precisely this database does not provide a better way of storing the user as an entity.

The entity properties are spread out in multiple tables and due to being optional you are taxed to do left joins with multiple tables.

So you have to essentially solve that problem first. You have several options or (non-options without knowing requirements.)

Put them in one table

If you can use nullable columns and refactor your application - this is preferable. I see that your other tables have just one or two more fields. Heavily normalized tables saves some space and with no data repetition other normalization benefits are moot.

Join them all with 1 request

Only if your query stays performant. Can you use left join ? You would do this if the above option is difficult. Use this only as a quick fix.

Other options To solve Database Problem

  1. Use server side caching if feasible.
  2. If feasible use a different NoSQL database (e.g. MongoDB)

Change the way the requests are made?

Do you really need all the properties upfront ? The web is asynchronous so why not keep things async. Use $q.all only if you need all the properties. For example the user may not even navigate/scroll to certain part of the page to see stuff so why may queries in the first place.

Along with this you can cluster your server side and the database so that these queries fall on multiple machines and load gets distributed. You may get some items retrieved in parallel.

If the number of columns are all that you have and the tables are all that you mentioned i.e. the supplemental tables have fewer properties I would go with Put them in 1 table option.

Upvotes: 2

dmfay
dmfay

Reputation: 2477

Why doesn't using nullable fields sit right with you? NULL exists because it's useful, and a profile table with optional values for a fixed set of fields is practically the textbook case for invoking it. If fields can be dynamically redefined (eg swapping out "favorite color" for "favorite food"), it's another story, but that's not a requirement in what you've described.

Upvotes: 0

Related Questions