1

I have one table news_feed_card which contains the following columns:

news_feed_card

id serial
card_type int
published_at date

Each news_feed_card can only have one news post. The problem is that I have two different news sources (from news sites & from twitter) and each news source has different properties. So I've created two tables source_article and source_twitter. Each table has a FK to news_feed_card.id.

source_article

id serial
title string
text text
url string
[FK] news_feed_card_id

source_twitter

id serial
user_id int
user_handle string
user_screen_name string
title string
text text
url string
profile_image_url string
retweet_count int
favorite_count int
[FK] news_feed_card_id

Is my approach correct? What I'm thinking is what if by accident have two news source linked to one news_feed_card. How can I avoid this?

2 Answers 2

0

There are not something like the rigth design. Each one has his own adventages and disadventages.

Other alternatives are:

  1. Put all columns in the same table (is more fexible if new sources apears, it is simplier, and you only has to add columns if the new source has more attributes)
  2. Put the main columns (i.e. title, source_id, text, url, date, card_type) in the news_feed table and add two tables one for the other attributes (i.e. table news_feed_attrs_value with columns news_feed_id, attr_id, value) and other for the attribute definitions (i.e. attr_id, attr_type). Is more flexible but more complicated.
  3. The same of option 2 adding news_feed_attr_source for having a white list of attributes for each source. Is more complicated and the flexibility is bounded.
0

Here is an approach you can try.

You can create a source table and two subset tables - source_article and source_twitter.

The relationship between news_feed_card and source table is one-to-one. The source table has the fields common to both article and twitter as shown below.

id serial
title string
text text
url string
news_feed_card_id [FK with unique constraint] 

This will allow only one source record for a news_feed_card record.

An alternative is to have the news_feed_card_id as the primary key for this source table.

The source_article and source_twitter tables:

source_article:

id serial
source_id
...other fields specific to source article

source_twitter:

id serial
source_id
...other fields specific to source twitter

Optionally, you can include source table field(s) like title within the source_article and source_twitter tables depending upon your querying needs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.