I want to create a table called "Users" for a message board. There are three fields so far:
2.Messages (for number of messages posted)
3.Replies (for number of replies posted)
However, I want to record the number of replies each message receives.
For instance, a user has posted three questions and received two replies to the first message, four replies to the second message and no replies to the last message.
How can I do this when I create the table?
You should tell me more about your design.How do you store your message or reply body? Is it referenced by a unique number for each message or reply?
If you design a Message tables and another table for Reply, so there should be a relationship between User table and these two tables.So your query will be done by just two joins and a simple count(replyID),for example.
Another opinion ... I'd say you can't, at least using the table you provided. It shows only number of a user's messages and replies, but you can't figure out anything about a single message.
Perhaps you could consider another approach - a table like this one:
create table messages
select * From messages;
msg_id user_id msg_type
------ ------- --------
1 100 M
1 250 R
1 334 R
2 534 M
3 234 M
3 112 R
(where M stands for "message" and R for "reply").
Now it is quite simple to do different selects, for example "How many messages did user 334 post?" (select count(*) from messages where user_id = 334), "How many replies were there for a mesasge number 3?" (select count(*) from messages where msg_id = 3 and msg_type = 'R'), etc.
Of course, you'll need another tables to fulfill the model (users, messages themselves, etc.).
P.S. Didn't quite understand what you meant by "do this when I create the table" ... rows can't be updated / inserted / whatever unless you do it through your application (Forms, stored procedures, database triggers). CREATE TABLE statement doesn't provide such a functionality.
I think I'll go with Littlefoot's suggestion, as I will need a table for messages anyway, and giving each message an individual id and a message type should allow me to count how amny replies a message has received.
Thanks to both of you for your replies.
Unjfotunatly I had not trace your thread because of many mails I had and...
The approach of littlefoot is not proper for u ,I think.
It does not model the relationship between A message and its replies.As I told you before you could have this modeling:
Users(uid,uname,...) : that uid is PK,
Messages(uid,mid,mbody,...) : that (uid,mid) is PK and uid is FK to users,
Replies(uid,mid,rid,rbody,...) : that (uid,mid,rid) is PK and (uid,mid) is FK to messages
Do not forget the principles of data modeling this is a simple case which named by "cascading relationship" in datamodeling:
A (1)------>(n) B (1)------->(n) C and .....
The Message_id ties message and all replies to each other.
The user_id allows you to find all the messages that the user generated.
If The Parent_id is null, it is the original message. If the Parent_id is not null, it points to the Messge_id that the reply is attached to (orignal message or other reply). This allows you to keep track of the flow of replies.
You do not need a parachute to skydive. You only need a parachute to skydive twice.
you may also add a Mtype column to differentiate the Questions from Answers.Ofcourse you can do this by selecting "not null" parent_ids that means this message is starter or a Question but this query may be somehow performance degrading.
add a "self referencing" relationship to my last posted model on Replies to themselves but if your application needs are not growing, I prefer Bills way.
I see this as the old BOM system, of bi-directional links. Each reply has a parent message, and each parent message can be either a reply or a grandparent message. You need to identify the top of the chain. There is only 1 original message, everything under that is a reply, which in turn can also have multiple replies. From this perspective, it is easy to see that this is a tree structure. Since you do not know where you will be entering the tree, you must have bi-directional links. Each message ( reply) should have the key of the parent message. To simplify lookups (but not totally necessary) each message should have a recurring table listing all replies. This would make the message table contain
parent message id
recursive message id of replies
Anybody remember how to set up the bill of materials... CS 301..
For maximum flexability, this should be the same....