Open jcotton42 opened 2 years ago
A rough pseudo schema for this
story_recs
id (pk, autoinc)
title
author_id (fk to story_authors.id)
summary (maybe?)
rating (general, teen, mature, explicit; like on ao3)
story_urls
id(pk, autoinc)
story_id (fk to story_recs.id)
url (canonicalized)
i.e. turn https://www.fanfiction.net/s/7087658/1/Backpack into https://www.fanfiction.net/s/7087658, also change m.fanfiction.net into www.fanfiction.net, etc.
story_authors
id (pk, autoinc)
I'm not sure if this table is necessary?
story_author_profiles
id (pk, autoinc)
author_id (fk to story_authors.id)
pseud
url (canonicalized)
i.e. turn https://www.fanfiction.net/u/2715794/CoronaIgnis into https://www.fanfiction.net/u/2715794 and https://archiveofourown.org/users/Hazama_d20/pseuds/Hazama_d20 into https://archiveofourown.org/users/Hazama_d20
note that fanfiction.net also supports https://www.fanfiction.net/~CoronaIgnis, we probably should reject these since they break if the author ever changes their pseud
I'm unsure how to elegantly handle multiple AO3 pseuds though?
unique constraint on (url) or (url, pseud) (to support multiple pseuds per profile)
story_rec_reaction
PK(story_id, user_id)
story_id (fk to story_recs.id)
user_id (discord snowflake)
reaction (1 for like, 0 for dislike)