i have unusual situation regarding db table splitting; have table named 'message' holding info messages sent via email:
message status -------------------------------- ------------- id | subject | body | status_id status_id | caption -------------------------------- ---------------------- 1 random bla 3 1 draft 2 second hello 3 2 scheduled 15 subj ok 1 3 archived when message created, it's status automatically set draft, can scheduled, , finally, when sent @ least 1 contact, it's status archived.
my boss suggests messages should split in three tables named
draft,
scheduled ,
archived.
in practice, everytime search message in db sending (status 'draft'), table contains large amount of unneeded data (messages status 'archived'). but, if split table, everytime change status of message, have insert corresponding table, , delete current status table.
since haven't heard of such practice before, ask opinion. valid split tables? in advance
you don't need split tables this. optimized queries should add more indexes, particulariy can use index on status_id column. dont see point of having statuses in separate table. if need caption can use status_id, otherwise can use regular integer.
