A.Ku A.Ku - 9 days ago 6
MySQL Question

Is there alternative to array in field in MYSQL?

I have 2 tables namely userinfo and newsinfo. Each user can add/remove any news feed as they wish. So at any time, news will only be delivered to those who subscribed to each news providers only. Users in table userinfo and news providers in table newsinfo will be added or removed as time goes by. I'm using php as the front-end.

Table: userinfo

+---------------+-----------------+--------------------+
| index | name | channel_list |
+---------------+-----------------+--------------------+
| 0 | aaaa | 0,1 |
| 1 | bbbb | 0,1,2,3 |
| 2 | cccc | 3,4 |
+---------------+-----------------+--------------------+


Table: newsinfo

+---------------+-----------------+
| channel | provider |
+---------------+-----------------+
| 0 | cnn |
| 1 | bbc |
| 2 | fox |
| 3 | rtl |
+---------------+-----------------+


Currently what I did was to use array in userinfo.channel_list to store subscribed news channel.

Is there any better way to achieve this without using array in field?

Answer

create a many-to-many relation with the following additional table (userinfo_newsinfo):

+-------+------+  +---------+-------------+  +-------------------+
| index | name |  | channel | provider    |  | userinfo_newsinfo |
+-------+------+  +---------+-------------+  +--------+----------+
|   0   | aaaa |  |   0     |    cnn      |  | index  |  channel |
|   1   | bbbb |  |   1     |    bbc      |  +--------+----------+
|   2   | cccc |  |   2     |    fox      |  |  0     |    0     |
+-------+------+  |   3     |    rtl      |  |  0     |    1     |
                  +---------+-------------+  |  1     |    0     |
                                             |  1     |    1     |
                                             |  1     |    2     |
                                             |  1     |    3     |
                                             |  2     |    3     |
                                             |  2     |    4     |
                                             +--------+----------+

Now you can remove the column channel_list on table userinfo and translate it to the new table.

Note: A column like channel_list should not be used! Use normalization to check and improve your database structure. The new table userinfo_newsinfo improves the database structure by considering the normalization.

To create this new table you can use the following SQL:

CREATE TABLE userinfo_newsinfo (
    `index` INT NOT NULL,
    `channel` INT NOT NULL,
    FOREIGN KEY (`index`) REFERENCES userinfo(`index`),
    FOREIGN KEY (`channel`) REFERENCES newsinfo(`channel`)
);

To select all channels of a user you can do the following query:

SELECT newsinfo.provider 
FROM userinfo u 
    INNER JOIN userinfo_newsinfo un ON u.index = un.index 
    INNER JOIN newsinfo n ON n.channel = un.channel
WHERE u.index = 0
Comments