Sql-server – News Database Design for a Desktop Application

database-designsql server

I am making an application that will process a big chunk of information. This information was retrieved using some web crawlers and is about news, containing data such as News Title, URL, Publication Date, Category and Content. The crawled data is in XML format, and I will load that information into my application.

From there, 10 users will process every news and tag the category of the news manually by reading the title. I've used 9 main categories to be used, and for each news the users will decide from 0-5 how much this particular news belongs to each category.

Users will also search for news by title and decide if this news is discussing the same event as another news, or if this news is similar to another news (like a news about a football game at 3 PM and a news about a football game at 5 PM).

I have no problems with making the application itself, I just need some help with the part of how to design one/many tables that can link news that talk about similar events or about the same event, since there can be many many news that can talk about the same event.

So far I've done something like this:

Table News

ID
Title
URL
PublicationDate
NewsContent

Table Category

NewsID
User_ID
Economy
Politics
Present_Day
Sport
Technology
Showbiz
Culture
Region
World

Table User

ID
FirstName
LastName

Each category field in the Category table holds a tinyint data type (I'm using SQL Server) and I've restricted the values the user can input via a check constraint so that it allows values from 0 – 5. I don't know if this is the right approach so far for the database design, and I need to add the table/s that allow to add information about similar news or news that talk about the same event, such as a table Similar_News containing some fields like News_ID, SimilarNews1_ID, SimilarNews2_ID and so on, and must contain the information about which user 'said this', but this sounds like a flawed design to me.

Any help is appreciated, thank you.

Best Answer

Based on how dynamic this sounds like I would suggest looking at a NoSQL data structure. I do not use them so much myself, but looking into something like MongoDB might be worth it. Good luck on your application!

Related Solutions

Questions about Database Design for a Web Application

100k records in a single non-partitioned table is a relatively trivial number. Nothing to worry about assuming you follow normalization guidelines and index appropriately.

Take a look at some example schemas to avoid schoolboy errors. Study sample databases, like the Microsoft examples for SQL Server. When you've created your schema, post a new question for feedback.

Mysql – How to design a table for storing news items

A few things you will have to think about before starting:

How do you decide if a title is the same as a previous one? Say there is an article 'A god table for storing news items'. Your script discovers and stores it in your DB. Later the editors of that news site notice the typo and correct the title as 'A good table for storing news items'. What do you do in such a case? (The same applies to the content.)
If you want to do something with your data after storing, you will need a unique identifier in the table. Using this, you will be able to point to an item from other tables.
You may find useful to store the URL of the articles. However, from time to time these can change just like titles do.
Short news are typically found in many places on the internet. Both their title and contents are the same (like in the case of short news from news agencies) - are these the same or not? If you store the URL, then these are different. If not, these are the same. What to do with these?
If you decide that you will store these from each site you process, than you will find useful to identify your source sites. (Be careful, the URL will look like 'http://dba.stackexchange.com/questions/23568/how-to-design-a-good-table-for-real-time-news-extraction' and the site will be 'dba.stackexchange.com'. Here the latter forms a part of the former, but this is not necessarily true for other sites.) In this case, you will have to create another table where you list all the source sites. This table will be referenced from your other table.

So far we have the following database structure:

Table "news_item"

+--------------+------------+--------------+---------------------+-----+-----------+
| news_item_id |   title    |   content    |     time_created    | URL | source_id |
+--------------+------------+--------------+---------------------+-----+-----------+
| 1            | First item | some content | 2012-09-03 22:53:14 | ... | 1         |
.                                                                                  .
.                                                                                  .
.                                                                                  .
+--------------+------------+--------------+---------------------+-----+-----------+

Table "source"

+-----------+-----------------------+
| source_id | name                  |
+-----------+-----------------------+
| 1         | dba.stackexchange.com |
.                                   .
.                                   .
.                                   .
+-----------+-----------------------+

You can write your SQL script only after answering at least the above questions.

Related Question