Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2004
    Posts
    7

    Unanswered: Create a sub-row of a row?

    I want to create a table called "Users" for a message board. There are three fields so far:
    1.Username
    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?

  2. #2
    Join Date
    Mar 2004
    Posts
    370

    One solution

    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.

    User---->Message---->Reply

    -good luck!

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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:
    Code:
    create table messages
    (msg_id  number,
     user_id number,
     msg_type varchar2(1)
    );
    
    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.

  4. #4
    Join Date
    Nov 2004
    Posts
    7
    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.

  5. #5
    Join Date
    Mar 2004
    Posts
    370

    Sorry,it seems wrong...

    Hi,
    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 .....
    -Best wishes

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What about replies to a reply?

    Or are you only going to allow replies like this board?

    1 Parent thread, and all replies are replies to the parent?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I would do it like this (pseudo code follows)

    Table - USERS
    user_id number;
    user_name varchar2

    Table - Messages
    Message_id number;
    user_id number;
    Parent_id number;
    message_body varchar2(4000);

    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.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Don't forget to add a date...

    I think I would use a nested table for this.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by Brett Kaiser
    Don't forget to add a date...

    I think I would use a nested table for this.....
    Yes you should have a create date and modify date if the application allows editing. Afterall, this was pseudo code, not the entire ball of wax.

    You want the number of messages then
    select count(*) from messages where parent_id is null and user_id='xxx';

    Your want the number of replies for a message then
    select count(*) from messages where parent_id is not null and message_id=123;
    Last edited by beilstwh; 12-15-04 at 17:35.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Mar 2004
    Posts
    370

    Also add a Message type!

    Hi friends,
    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.

    Another option:
    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.

    -good luck!

  11. #11
    Join Date
    Nov 2004
    Location
    Temple University
    Posts
    36

    Cool Bill Of Materials

    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
    message ID
    User ID
    message
    parent message id
    recursive message id of replies

    Anybody remember how to set up the bill of materials... CS 301..
    part id
    parent part
    child parts,
    etc...

    For maximum flexability, this should be the same....

    HTH
    Peter...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •