Ways to store different kinds of messages in a DB

Question

I am wondering what are the possibilities for storing this kind of data in an efficient way.

Lets say I have 100 kinds of different messages that I need to store. all messages has a common data like message Id, message name, sender, receiver, insert date etc. every kind of message has it's own unique columns that we wanna store and index (for quick queries).

We don't want to use 100 different tables, it will be impossible to work with. The best way That I could come up with is to use 2-3 tables: 1. for the common data. 2. for the extra unique data when every column has a generic name like: foreign key, column1, column2....column20. there will be index on every column + the foreign key(20 indexes for 20 columns). 3. optional, metadata table to describe the generic columns for every unique message.

UPDATE: lets say that I am a backbone for passing data, there are 100 different kinds of data(messages). I want to store every message that comes through me, but not as a bulk data because later I would like to query the data based on the unique columns of every different message type. Is there a better way out there that I don't know about? Thanks. BTW the database is oracle.

UPDATE 2:

Does NoSQL database can give me a better solution?

Answer 1

The approach of 2 or 3 tables that you suggest seems reasonable for me.

An alternative way would be to just store all those unique columns in the same table along with common data. That should not prevent you from creating indexes for them.

Answer 2

Here is your answer:
You can store as much messages as you want against each message type

UPDATE: This is what you exactly want. There are different messages type as you stated e.g. tracking, cargo or whatever. These categories will be stored in msg_type table. You can store as much categories in msg_type as you want.

Then say each msg_type has numerous messages that will be stored in Messages table. You can store here as much messages as you want without any limit

Here is your database SQL:

create table msg_type(
type_id number(14) primary key,
type varchar2(50)
);

create sequence msg_type_seq
start with 1 increment by 1;

create or replace trigger msg_type_trig
before insert on msg_type
referencing new as new
for each row
begin
select msg_type_seq.nextval into :new.type_id from dual;
end;
/

create table Messages(
msg_id number(14) primary key,
type_id number(14) constraint Messages_fk references CvCategories(type_id),
msg_date timestamp(0) default sysdate,
msg varchar2(3900));

create sequence Messages_seq
start with 1 increment by 1;

create or replace trigger Messages_trig
before insert on Messages
referencing new as new
for each row
begin 
select Messages_seq.nextval into :new.msg_id from dual;
end;
/