Db design

Hello everybody,

Well, i need some more clarification regarding the same question again.I guess db admins will have more idea about it.

Question: topic, under that sub-topics,under that articles

For each topic there could be some 20 sub-topics and for each subtopic there could be some 1000 or 2000 or more articles.

For example, if i have a topic 1 and under that sub-topic1, sub-topic2, sub-topic3, sub-topic4 and if have 1000 or 2000 articles for each subtopic, then i cant imagine the size now;-)

So, could you please tell me the best idea how i can get the design.

But, this is true: For each topic, there are many sub-topics and for each sub topic there are many articles.

My previous design was: 3 tables-[topic(topic_id, topic_name), subtopic(subtopic_id, topic_id, subtopic_name), articles(article_id, subtopic_id, article_name, article_content)

How could you suggest me now??each topic a table or some thing like that.

A nice explanation would help me more.

Thank you so much

Hi Chinni,

Actually, your design is pretty sound from a relational database perspective. Yes, with thousands of articles, the DB would get big. But really what will make it big is the content itself in each article as the table row sizes are fine.

The only optimization I can picture is to reduce two tables to one. What I mean by this is that a subtopic is really nothing more than a topic with a parent, right? Also, assuming subtopics can't belong to more than 1 topic, this might look like:

Topics
-------
topic_id
topic_name
parent_id

Articles
--------
article_id
parent_id
article_content

Your top level topics then could have null or 0 as the parent_id which would tell you it's a main topic.

Advantages:
- Reduces number of tables by 1
- Allows greater flexibility in that you could add additional layers (subtopics of subtopics!) without a database schema change

Disadvantages:
- More records per table...Could impact search performance (but would have to be weighed against the performance cost of joining the third table).
- This is actually not quite third normal form as you have columns that contain lots of nulls in this design (your original design is more 3NF).

So, weigh these and go for it!

Just my $0.02...Good luck with that.

Hello davis,

Thanks for your explanation.sounds meaningful.

yes, thats true - Subtopics cant belong to more than one topic.

Well, I would like to add subtopic_name in the table topic and article_name,article_dateandtime in the table article.

will that gonna affect the design?

I just wonder, I better give a try and see if there are any risks.

Thanks for your time

Hi Chinni,

According to the 2-table model I described, instead of adding another column for subtopic name, you could the more general topic_name for that purpose. What makes it a "subtopic" is the fact that it has a parent_id that is not 0 or Null.

For example:


Adding the additional columns to the Articals table shouldn't be an issue. And in this example, the Parent_ID field in the Articals table would reference back to the Topic_ID in the Topics table (called a foreign key).

Hope that helps,

Bah...Tried to format it with the Code tag but it didn't work. Sorry about that. ;)