search

jstockwin / Film-Night

0 stars 0 forks source link

Using a better normalized database #66

Closed unpairedbracket closed 8 years ago

unpairedbracket commented 8 years ago

Some of the php that handles database interaction could be made cleaner if we restructure the database somewhat. For example if we were to add nice autoincrementing primary keys to our users and films tables, and move the proposed_by and vetoed_by columns into a proposals table that contains a user id, a film id and a boolean is_veto field, we could use the SQL query SELECT films.* FROM films INNER JOIN proposals ON films.id = proposals.film_id INNER JOIN users on users.id = proposals.user_id GROUP BY film_id HAVING NOT SUM(is_veto AND NOT attending); to return all films not currently vetoed by a non-attender, and even generate the film list by sticking ORDER BY RAND() LIMIT 5 onto the end of that query.

If I were making the db from scratch now I'd also replace the selected_films table with one that just joins into the films and timings tables, and have, say, 5 rows linking film ids to each timing row. We might even be able to insert into it directly from the above film selection query, just by using a SELECT INTO query. Then when someone casts their vote we could add 5 rows to a votes table that links the selected films to the user who's voted and has an int field for the position they've put it in. That would allow us to ditch the incoming_votes table and keep a record of all the voting that's happened - we'd just be able to do a SELECT by film night id (joining votes to selected_films) and show that set of results.

I realise implementing this and migrating all the data over will be a lot of work and it may or may not be practical to do this at any point, but if there's going to be a time to do it, it'll be over the holidays when we don't need to be in production for a few weeks. If we do decide to do it, I'm happy to take a copy of the db and do the grunt work myself in the holidays, so we can return next term with a shiny new database and less horrible database management code.

jstockwin commented 8 years ago

I'm happy to make a films2 database, and effectively start the DB from scratch, but as you say, it's the data migration that is a lot of effort.

unpairedbracket commented 8 years ago

I made a pretty picture, mostly for my reference if I do end up doing this, and because I have work to do so I had to find something else to do instead. dbschema

jstockwin commented 8 years ago

Very nice. Perhaps worth including things like the current user id etc. We will still need this for logging in. Happy to give you an account on the pis with access to the DB if you like.

unpairedbracket commented 8 years ago

Ah, yeah, of course. I'd move all the user settings, etc. into the users table too, and possibly the film info that's currently put in selected_films by imdb.php into the films table. Was just mostly including the fields necessary for all the linking to work etc. Sounds good - typical that I think to do this right at the point where I've got the most work I've had all term though... I'll look further into it at some point next week once I've got all these sheets out the way.