search

RDBinns / datactrl

Making the UK register of data controllers more useable
Apache License 2.0
2 stars 1 forks source link

Tweak sql tables #7

Open RDBinns opened 10 years ago

RDBinns commented 10 years ago

At the moment there are 5 tables, so queries which need to join all of them take ages. Maybe we should put them all into one big table that's faster to query.

themakshter commented 10 years ago

I'm currently working on a MongoDB solution in my version of the project. Would that work?

RDBinns commented 10 years ago

Yes! From what I understand of MongoDB (not much), that would be a better solution. Am I right in thinking it stores the data in something like JSON? If so, this could also make it easier when building the front end in javascript.

themakshter commented 10 years ago

Yes, it does. I'm currently working on this , but firstly parsing the data I have in Java. I'll send you an email about it.

RDBinns commented 10 years ago

My current thoughts on how this could work: Currently, given the mysql implementation, if you want to get all the information from one data controller (say, datactrl_id = 1), you have to write a query joining multiple tables like this:

select datactrl_name,purpose_name,class,subject,recipient,transfer from purpose inner join purpose_classes on purpose.purpose_id = purpose_classes.purpose_id inner join purpose_subjects on purpose.purpose_id = purpose_subjects.purpose_id inner join purpose_recipients on purpose.purpose_id = purpose_recipients.purpose_id inner join purpose_transfers on purpose.purpose_id = purpose_transfers.purpose_id inner join datactrl on purpose.datactrl_id = datactrl.datactrl_id where purpose.datactrl_id = '1';

This is obviously quite slow because of the multiple table joins. It would be better to pre-process this, so we can have a json object for each entry instead. These could then be loaded into MongoDB and called up quickly and easily (I'm guessing here). I imagine a typical user will want to check 5-10 entries at a time.

If I have time I'm going to try following this tutorial to test this out on a subsection of the register http://www.anthonydebarros.com/2012/03/11/generate-json-from-sql-using-python/

themakshter commented 10 years ago

http://pastebin.com/YVW27bTm an example of how this could be represented in JSON

RDBinns commented 10 years ago

Excellent! This is just what we need to get out. Will have a go at the python tutorial referred to above soon, maybe tomorrow.