481303 - Design new schema for database table for storing l10n dashboard data

Status: RESOLVED FIXED

(support.mozilla.org :: Localization, task, P1)

Description

[E. Cooper [:ecooper]]

We'll be adding a table to the current SUMO database instead of relying on the tracker functionality built into tikiwiki.

What will need to be stored in the table can be found under the second item of this list https://wiki.mozilla.org/Support/l10nPriorityPRD#Implementation 

I'll have a suggested schema attached shortly.

Comment 1

[E. Cooper [:ecooper]]

David I've been playing around with this as I've been developing the library for bug 418305.

Are we really going to see a difference between 'score' and 'priority'? Would it be easier to just have a general field/column called 'weight'?

Then we could have a simple, small three-column table: page, type (think 'list', like kb, navigation, etc), weight.

Comment 2

[David Tenser [:djst]]

The score can theoretically be the same on more than one article. In our case, we'll use the relative page hits % as the score, so something like 0.0145 might be a score for any given article (that is, 1.45% of the total page hits of the articles in that list). What if both article #7 and #8 in that list has the same score of 0.0145 but we still want one article to be sorted before the other? 

Or what if we want to have the list prioritized on popularity (as a combination of page hits, poll votes, and searches), but we still want to keep our 50% page hits baseline? In that case, the order of articles in the list will be relatively independent of the score. I'd say we keep the priority field, even though it will essentially just be an int starting from 1 to number_of_items in the list.

About the type field, do you mean that we would use the same table for all lists? That sounds doable, but then I'd create another table schema for the list types and point to its primary key in the other (big) table. Does that make sense?

Comment 3

[David Tenser [:djst]]

To clarify, the tables would look something like this (# = primary key):

TABLE: articletype
 # articletype_id
 articletype_title: (string)
 articletype_description: (string)

TABLE: articlelist
 # articletype_id (reference to articletype table)
 # articlelist_priority: (int)
 articlelist_score: float
 articlelist_article

Note: my db skills are limited, so I might be missing something obvious, or overdesigning this. Use your best judgment. :)

Comment 4

[David Tenser [:djst]]

DOH! s/articletype/listtype/

Comment 5

[E. Cooper [:ecooper]]

Attachment: Schema of new article list tables

I thought about this for a while, and at first I thought it might be a bit overkill...I mean, if we expand it too far, it ends up just being pseudo categories. After playing with this schema and modeling the library around it, I'm with David's proposal with one minor change--using list_type_id and page_name as primary keys instead of list_type_id and priority.

Using page_name over priority simplifies updating lists. I'm not too worried about duplicate priorities because they will be calculated by the library before insert/update.

Comment 6

[E. Cooper [:ecooper]]

Comment on attachment 366204 [details]
Schema of new article list tables

Ugh. Wrong table. Ignore this.

Comment 7

[E. Cooper [:ecooper]]

Attachment: Schema for new tables (v2)

Apparently, I mislabeled the SQL patches locally. This should be the one mentioned comment 5.

Every time I work over a Sunday, I do something like this. :(

Comment 8

[David Tenser [:djst]]

Comment on attachment 366205 [details]
Schema for new tables (v2)

Looks good to me. page_name will be unique for every list anyway, so works as primary key together with list_type_id.

Comment 9

[E. Cooper [:ecooper]]

I just had a major duh moment.

We need to get these tables on staging before we test. What's the preferred method here? Can we just run with the raw SQL? Or do I need to make an update script?

Comment 10

[David Tenser [:djst]]

Forgive me Laura for possibly saying the wrong things here, but I think it's fine to run the SQL statements as long as you document them so we can re-run the same commands on prod when we push.

Comment 11

[Laura Thomson :laura]

Comment on attachment 366205 [details]
Schema for new tables (v2)

Needs foreign keys list_type_id and likely page_name, other than that ok.

Comment 12

[E. Cooper [:ecooper]]

Tiki doesn't use foreign keys as far as I could see, so I didn't either to be consistent.

Comment 13

[E. Cooper [:ecooper]]

The tables are up in staging: https://bugzilla.mozilla.org/show_bug.cgi?id=483966

We're currently working on getting the cron script going as well.

Comment 14

[E. Cooper [:ecooper]]

I'm going to mark this as resolved for the time being. If we run into related problems, we can reopen.

Comment 15

[David Tenser [:djst]]

LP: Could you please verify that the db schema for the l10n dashboard was also upstreamed?

Comment 16

[Louis-Philippe Huberdeau]

Yes, they were added.

20091021_moz_pagelist_tiki.sql