Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2014

    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?

  2. #2
    Join Date
    Nov 2014
    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.
    Click image for larger version. 

Name:	dbforums_ex1.png 
Views:	4 
Size:	11.6 KB 
ID:	15960

    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.
    Click image for larger version. 

Name:	dbforums_ex2.png 
Views:	4 
Size:	14.3 KB 
ID:	15961
    Hope this helps.
    Last edited by patrycja; 11-11-14 at 15:58.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts