SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    messaging system creation

    Hi,

    I am building a private messaging system and I wonder if anyone could help me with confirming the structure is correct.
    Code MySQL:
    create table messaging_users
    ( email varchar(99) not null primary key
    , auto_password varchar(99) not null
    , first_name varchar(99)
    , last_name varchar(99) 
    , username varchar(99)
    ) engine.....
     
    create table messaging_initiation
    ( id bigint not null auto_increment primary key
    , title_of_message varchar(99) 
    , time_of_message datetime not null
    , message_body varchar(1000)
    , sent_by varchar(99) 
    ) engine....
     
    create table messaging_replies
    ( id bigint not null auto_increment primary key
    , initiation_id bigint not null
    , title_of_message varchar(99)
    , time_of_message datetime not null
    , message_body varchar(1000)
    , sent_by varchar(99)
    , add constraint replies_posts_fk
        foreign key (initiation_id)
          references messaging_initiation(id) 
    ) engine....

    Have I set the data type correctly for message_body?
    The tables are almost the same but for the FK. The FK is the only reason I created two tables so the constraint can be used to tie the replies to the original message. Is there a better way?

    Can you see any other cols that I have ommitted but which I ought to consider?

    bazz

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i would use TEXT for the message body

    i would also combine the two messaging tables into one -- you can have the FK reference the same table, that's done all the time
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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