| Author |
Topic |
|
maichau
Starting Member
5 Posts |
 Posted - 2009-05-19 : 12:42:34
|
hi am new in sql and am a Java Developer. I need to develop a discussion forum. I would be glad if anyone can give me some idea on how to design the database table for the said project.How to save all the comments from different users for a single thread.Please Please help me. thanks. A link that explains everything will be great. regards,maichau |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-05-19 : 13:31:16
|
This thread has a good discussion on how to go about designing a model:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47943Books online has a lot of good info as well. One place to start is to design your application first - all the screens and functionality (use cases) you expect the application to display and perform. From there you will have a better idea of what entities you will need to define and store as well as what business rules you will need to define and enforce.quote:
...some idea on how to design the database table
If you end up with just one table then...I'm not even sure how to finish that thought  Be One with the OptimizerTG |
 |
|
|
maichau
Starting Member
5 Posts |
Posted - 2009-05-20 : 01:01:30
|
| let me put this in a different way. Say in the main page, a list of topics available for discussion will be shown along with the auther, replies.... when you click on any one of the topics viz. "Disallow duplicate in a multiple scenario" http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47943, it takes you to the page which shows all the response from different users for the selected topic. My question is, do i need to create a table each time a new topic starts in the forum?@ TG,thanks for you response.regards,maichau |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-05-20 : 13:46:27
|
| generally speaking a relational database design will have 1 table per TYPE of "thing". Several "things" you mention in your last post are:Forum, User, Topic, and Response. One model could be to have 1 table per each of these things. The link you posted above includes a "TOPIC ID". That implies that a single TOPIC table would contain many TOPIC Ids each identifying a specific topic. So if you re-read you own question you may see the answer - that you should NOT create a new table for each topic, just another row in the topic table. Is this for a class assignment or your own personal (non-work) project or have you been tasked to create this for your job?Be One with the OptimizerTG |
|
|
|
maichau
Starting Member
5 Posts |
Posted - 2009-05-28 : 02:48:59
|
| Thanks TG for your response. This was not a class assignment; its just an out of work project. I have already figured out the solution for my problem. It turns out to be very simple. All i needed is just two tables: 1. Table that contains all the (pk)thread ids and thread name.2. Table that contains all the response and thread id as the foreign keyThanks,maichau |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-28 : 03:06:36
|
Or why not just table?And have a "answernumber" column which is set to 0 for threads, and 1..2..3..4 for every answer thereafter?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
maichau
Starting Member
5 Posts |
Posted - 2009-05-30 : 05:10:01
|
| hi Peso,I would be glad if you could explain it in more details. Regret that am not able to grasp the point you are trying to make.maichau |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-30 : 06:02:45
|
[code]CREATE TABLE Categories( CategoryID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, CategoryName VARCHAR(200))CREATE TABLE Topics( Topic ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, CategoryID INT REFERENCES Categories(CategoryID), Header VARCHAR(100))CREATE TABLE Messages( MessageID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, TopicID INT REFERENCES Topics(TopicID), AnswerToID INT REFERENCES Messages(MessageID), Message VARCHAR(1000) not null, AnswerLine SMALLINT DEFAULT 0 not null, MessageDate DATETIME DEFAULT GETDATE() not null)[/code]This way, you insert the header of starting topic in the TOPICS table,and post the original message in the message table and set answer to 0 (indicating original message).For every answer to a specific topic after that, add a response with text to the message table and increment the AnswerLine column value;0 - Original message1 - First reply2 - Second replyx - and so on...This way you can display the complete thread in a simple way. You can also use MessageDate for this, depending on MessageDate is client local or UTC time.You can also use the AnswerToID column, which gives you the oppurtunity to display the complete thread in a hierarchy.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
maichau
Starting Member
5 Posts |
Posted - 2009-05-30 : 08:59:41
|
| Mr Peso,Please accept my heartfelt gratitude. You have made my work almost completed.warm wishes,maichau |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-05-30 : 09:47:23
|
| >>page which shows all the response from different usersTo support this you might want to consider a [Users] table and a [UserID] in your [Messages] table and perhaps in your [Topics] table as well - if you want to know who created the topic. That way you can enforce possible rules like: you can't edit or delete someone else's message. That's why I suggested designing all your screens and rules first. That can avoid a lot of going back and re-working your data model every time you think of a new bit of functionality.Be One with the OptimizerTG |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-30 : 09:52:16
|
AnswerLine seems to be obselete too. You can write a recursive CTE to deal with displaying the topics in the thread.If you want to display the messages in a flat style without AnswerLine, just write"ORDER BY MessageDate, MessageID". MessageID is just in case there are two messages saved for same topic the very same millisecond.And as TG ways, add User and thus some security. I just provided a starting shell.If you use the "AnswerToID", you also ensure that you cannot delete a message which has been answered.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
table design for Discussion Forum?