mysql - Ticketsystem: sql tables -

i not in creating mysql tables.

i want create small ticket system, user can apply following information:

• ticket username
• ticket email
• question

after submitted, want information stored in database table.

now supporter , user should able add comments ticket.

i tried create tables, little worried if work way:

create table if not exists `tickets` (   `id` int(11) not null auto_increment,   `username` varchar(20) not null,   `email` varchar(50) not null,   `ticket_id` varchar(30) not null,   `ticket_question` varchar(255) not null,   `ticket_status` tinyint(1) not null default '0',   `ticket_creation_date` timestamp not null default current_timestamp   primary key (`ticket_id`),   unique key `username` (`username`) ) engine=innodb  default charset=utf8 auto_increment=203 ;  create table if not exists `conversation` (   `id` int(11) not null auto_increment,   `ticket_id` varchar(30) not null,   `ticket_comment` varchar(255) not null,   `ticket_latest_reply` timestamp not null default current_timestamp   primary key (`ticket_id`),   unique key `username` (`username`) ) engine=innodb  default charset=utf8 auto_increment=203 ; 

could have @ or correct me?

i think you'd able add more 1 comment ticket. in way setting tables isn't possible, 'cause setting ticket_idas primary key.

you should set id in conversationtable pirmary key , ticket_id foreign key ticket tables ticket_id, because can have multiple comments 1 ticket

create table if not exists `tickets` (   `id` int(11) not null auto_increment,   `username` varchar(20) not null,   `email` varchar(50) not null,   `ticket_id` varchar(30) not null,   `ticket_question` varchar(255) not null,   `ticket_status` tinyint(1) not null default '0',   `ticket_creation_date` timestamp not null default current_timestamp,   primary key (`ticket_id`),   unique key (`id`),   unique key `username` (`username`) ) engine=innodb  default charset=utf8;  create table if not exists `conversation` (   `id` int(11) not null auto_increment,   `ticket_id` varchar(30) not null,   `ticket_comments` varchar(255) not null,   `ticket_latest_reply` timestamp not null default current_timestamp,   primary key (`id`),   constraint `fk_tickets` foreign key (`ticket_id`) references `tickets`(`ticket_id`)  on update cascade on delete cascade ) engine=innodb  default charset=utf8; 

attention: if delete ticket_id in ticket-table, delete comments related ticket (on delete cascade)

in case each comment stored in table.

if want conversation single conversation need additional table holding comments , link comments in conversation table id doing ticket_comment did above.

create table if not exists `tickets` (   `id` int(11) not null auto_increment,   `username` varchar(20) not null,   `email` varchar(50) not null,   `ticket_id` varchar(30) not null,   `ticket_question` varchar(255) not null,   `ticket_status` tinyint(1) not null default '0',   `ticket_creation_date` timestamp not null default current_timestamp,   primary key (`ticket_id`),   unique key (`id`),   unique key (`username`) ) engine=innodb  default charset=utf8;  create table if not exists `conversation` (   `id` int(11) not null auto_increment,   `ticket_id` varchar(30) not null,   `comment_id` int(11) not null,   `ticket_latest_reply` timestamp not null default current_timestamp,   primary key (`id`),   unique key (`ticket_id`, `comment_id`) ) engine=innodb  default charset=utf8;  create table if not exists `comments` (   `id` int(11) not null auto_increment,   `comment` varchar(255) not null,   `comment_last_update` timestamp not null default current_timestamp,   primary key (`id`) ) engine=innodb  default charset=utf8;     

and maybe think setting ticket_id primary key in tickets. when unique, can use primary key.