database design - mapping upvotes to contents in various tables
In the early stages of an application I'm developing, I had two relatively simple tables - one for content and one for upvotes. The content table's schema is as roughly:
data | text
id | integer
The upvotes table is roughly this:
id | integer
username | character varying(254)
content_id | integer
where the content_id is the id of the content from the content table.
This simple schema allows me to easily get all the upvotes for a particular content and vice versa.
Now, I'd like to add different content types and I'll be creating new tables for these different types. I'm trying to figure out how best to design the database. In my application, upvotes can be applied to any type of content (including the new types of content I'll be adding).
What's the best way to design this so that I can easily map upvotes to content types? I obviously cannot add upvotes that apply to contents stored in the new tables to existing upvote table - right now, the content_id column in the upvotes table assumes it's the id of content in the single content table that exists currently. I should note that I'm ok with dropping existing tables...just wanted to clarify that this isn't a question about migrating an existing schema; it's more a question of the right design for supporting upvotes for contents stored in different tables and maintaining a mapping between them.
One idea was to have a new upvotes table that adds another column that contains the "type" of content the upvote is being applied to which would serve as an indication as to which table the associated content is from, but this seems clunky.
The other option was just to have separate upvote tables for each type of content, so there'd be something tables contents_a upvotes_a, contents_b, upvotes_b, etc. This seems like it could get unwieldy as I add more content types in the future.
I'm sure there's a better way to think about this. Any suggestions?
I think the second idea is better. As you said yourself, the first option would be cumbersome, because you would have to make different joins depending on the value of one of the columns. You also loose the comfort of automatically checked foreign keys. https://my.vertabelo.com/public-mode...=5154&zoom=1.1
Whereas in the second option you wouldn't have to do those strange joins. Besides, you would have smaller tables which would be more efficient.