Many-to-many without a extra table

Question

I have two tables operation and source in mysql database.

In operation I have 10 rows(possibility) and in source just 3 rows(possibility) and between them there is a many-to-many relationship.
Question: Is it necessary to add this extra table or just add a foreign key of source in operation.

operation can be subscribe request, subscribe enabled , subscribe disabled , subscribe canceled , payment ok , subscribe deal ok , subscribe start.

source can be from internet , from agent

there is common operations and independent operations from source.

the operaion subscribe enabled can be done from internet subscribe or from agent subscribe and operation : subscribe deal ok can be just from agent and subscribe request can be just from internet.

Answer 1

In a relational database you need 3 tables to make a many to many relationship. Two containing the primary keys and the join table. There's no other way.

Answer 2

For the short and brief answer, normally, with an rdbms like mysql, where only one-to-many relations are supported, you need a 3rd (junction, or cross-reference) table to implement a many-to-many relation between two entities.

But....

Since you don't have too many records, you can map your many-to-many relation between source and operation with just one additional column in source and without redundant data storage. However, you may possibly loose some performance (e.g.: less powerful indexes) and definitely make your life harder working with these tables...

The trick is to use specific binary values as primary key values in your operation table, and add an integer column to the source table where you use its bits to map the relations. So one bit of this column describes one relation between the actual source record and the corresponding operation record.

For your sample operation table, you can create a table with a pri key of a bit type, with a size equal to your estimated number of rows. You say that you are going to have ~10 rows, so use bit(10) as data type. Since, mysql would store int on 4 bytes, you don't loose on the storage size here (instead, compared to int, you may actually win some, but it is really a matter of how the dbe is able to compress the records. actually, you could simply use int as well, if you wish.)

create table operation (id bit(10) primary key, title varchar(50));

insert into operation values (b'0', 'none');
insert into operation values (b'1', 'subscribe request');
insert into operation values (b'10', 'subscribe enabled');
insert into operation values (b'100', 'subscribe disabled');
insert into operation values (b'1000', 'subscribe canceled');
insert into operation values (b'10000', 'payment ok');
insert into operation values (b'100000', 'subscribe deal ok');
insert into operation values (b'1000000', 'subscribe start');

Now, suppose that you have the following in your source table:

create table source (id int primary key, value int, operations bit(10));

insert into source values (1, 1, b'0');
insert into source values (2, 2, b'1'); -- refers to subscribe request
insert into source values (3, 3, b'10'); -- refers to subscribe enabled
insert into source values (4, 4, b'10011'); -- refers to payment ok, subscribe request, subscribe enabled
insert into source values (5, 5, b'1110011'); -- refers to subscribe deal ok, subscribe start, payment ok, subscribe request, subscribe enabled

Now, if you want to select all the relations, join these two tables as follows:

select source.id, operation.title
from source
join operation
  on (source.operations & operation.id);

id  operation.title
2   subscribe request
4   subscribe request
5   subscribe request
3   subscribe enabled
4   subscribe enabled
5   subscribe enabled
4   payment ok
5   payment ok
5   subscribe deal ok
5   subscribe start

If you want to add a new relation, you may take advantage of the on duplicate key update clause of insert, so you don't have to worry about existing relations:

insert into source (id,value,operations)
   values (2,2,(select id from operation where title = 'subscribe start'))
on duplicate key update operations = operations
   | (select id from operation where title = 'subscribe start');

If you want to delete a relation:

update source set operations = operations
   & ~(select id from operation where title = 'subscribe start') where source.id=2;

All in all, it is not a nice, but a possible way to map your many-to-many relation to just two tables.