0

I have an unusual situation regarding DB table splitting; I have a table named 'message' holding info for messages to be 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 is created, it's status is automatically set to draft, it can be scheduled, and finally, when sent to at least one contact, it's status is archived.

My boss suggests that messages should be split in three tables named

draft,

scheduled and

archived.

In practice, almost everytime you search for message in DB for sending (status is 'draft'), the table contains large amount of unneeded data (messages with status 'archived'). But, if I split the table, everytime I change the status of a message, I have to insert it to the corresponding table, and delete from current status table.

Since I haven't heard of such a practice before, I ask for an opinion. Is it valid to split the tables? Thanks in advance

Community
  • 1
  • 1
icemanblas
  • 93
  • 1
  • 1
  • 10

2 Answers2

1

You don't need to split the tables for this. For optimized queries you should add more indexes, particulariy you can use index on the status_id column. Also I dont see the point of having the statuses in a separate table. if you need the caption you can use that as status_id, otherwise you can just use a regular integer.

breakline
  • 5,776
  • 8
  • 45
  • 84
1

In my opinion, that creates unnecessary overhead when processing your messages. Using the status_id to filter your results should provide what you need to find messages in "draft" status. As long as you have the proper indexes in place on your table, performance shouldn't be an issue. At some point, depending on volume, you MAY want to truly archive those messages to another table, but that is purely a design consideration. It depends on whether you need to keep records of those messages for a specified amount of time.

geektampa
  • 108
  • 10