Table design issues - should I create separate fields or store as a blob

Question

Hi guys I'm working on my web based ordering system and we would like to maintain a kind of task history for each of our orders. A hsitory in the sense that we would like to maintain a log of who did what on an order like lets say an order has been entered - we would like to know if the order was acknowledged for an example. Or lets say somebody followed up on the order - etc.

Consider that there are numerous situations like this for each order would it be wise to create a schema on the lines of:

Orders
ID - title - description - date - is_ack - is_follow - ack_by .....

That accounts to a lot of fields - on teh other hand I could have one LongText field called 'history' and fill it with a serialised object holding all the information.

However in the latter case I can't run a query to lets say retrieve all orders that have not been acknowledged and stuff like that. With time requirements woudl change and I would be required to modify it to allow for more detailed tracking and that is why I need to set up a way which would be feasible to scale upon yet I don't want to be restricted on the SQL side too much.

EDIT ===================

So the blob idea has issues then :( but what are my options in this regards. I actually would wish to manage a history of what goes on with an order. Like if someone has:

• Acknowledged the order Followed up on
• the order Attached an email to the
• order Completed a task for the order
• Made a call etc

Answer 1

Mashing together logically distinct pieces of information is almost always a disaster. Reducing the field count is not a goal in its own right.

Answer 2

Very seldomly you need to store information as a blob, or xml.

When doing this you start loosing the ability provided by your database engine to query effectively.

Your queries would then either have to be handled with the application, or specialized db code, which would seem to over complicate things.

And just thinking about loosing the ability to index columns has my hairs on end X-)

Answer 3

The boolean fields you show in the example are usually not enough. I suggest you create other tables:

Status (ID, Value) - essentially enumeration of possible values: Received, Acknowledged, Dispatched, ...
OrderStatus (ID, StatusID (FK to Status), AuditBy, AuditAt, Comment)

You can avoid having Status table completely, and just have the Status column in the OrderStatus table. But in this case at least restrict the possible values to a list you have.

In this way you have much better audit trail of what happened and when and by whom.

Answer 4

not really thought this through but you could do something like this http://pastie.org/889605

some snippets from the pastie:

drop table if exists order_events;
create table order_events(
 event_id int unsigned not null auto_increment primary key,
 order_id int unsigned not null,
 event_type_id tinyint unsigned not null,
 status_id tinyint not null,
 emp_id smallint unsigned not null,
 event_date datetime not null,
 key order_events_order_idx(order_id),
 key order_events_emp_idx(emp_id)
)engine=innodb;


drop table if exists event_type;
create table event_type(
 event_type_id tinyint unsigned not null auto_increment primary key,
 name varchar(255)
)engine=innodb;

insert into event_type (name) values ('new order event'),('order status event');

create trigger orders_after_upd_trig after update on orders
for each row
begin
  -- change of status
  if new.status_id <> old.status_id then
  insert into order_events (order_id,event_type_id,status_id,event_date,emp_id) 
   values (old.order_id, 2, new.status_id, now(), new.updated_emp_id);
  end if;
end#