I've asked a version of the question here Allow Null Columns vs separate 1-to-many tables and here Multiple JOINs of possible properties in a 'flower' arrangement - acceptable design? but still not clear, possible because of the jargon used.

To be clear, a central item can have between 1 and 10 attributes assigned (typically 2 or 3). I'll try to present 3 approaches to this, as I see them:

  1. Currently, query on central table joins 10 other tables by central_table_id. After testing with 500,000+ dummy rows added to central table shows no slowdown. As soon as 1 attribute table gets 500,000 dummy rows added query slows 10x. Around 50 rows will ever be selected from central table at one time.

  2. I asked before about storing the the attribute_ids in the columns on the central table. I'm now leaning more to this after seeing the slowdown. Question is, should I have 10+ attribute columns storing attribute_ids, which I would still need to join to 10+ tables (I'm guessing this is quicker that searching the attribute tables by centra_id, but I just don't have the experience to say for certain), or...

  3. Store the attribute_ids in a JSON column and perform a follow-up ASYNCHRONOUS search of only the tables as listed in the JSON field in a promise (so ~50 simultaneous queries). How bad is this for an idea?

knex('central').where('external_id',1) .select( 'central.json' ) .then((results)=>{ var promises = results.map((result,i)=>{ var attributes = JSON.parse(result.json) return knex('central').where('external_id',1) .select(...) //for loop function to create appropriate select .modify(function(queryBuilder){ for (var attr in attributes){ //queryBuilder methods for where/andWheres and joins } }) }) }) Promise.all(promises) .then((all)=>{ res.send(all) }) })

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.