Using Views for a custom table with multi-value fields

Question

I have a custom table with two columns - the primary is simply entity IDs (nids), and then there is a data column, but it it can be a multi-value (from 1 to anything).

How can I structure the MySQL table and create a view that will blend a list of entities (nodes in my case) with data from the custom table without losing their multi-value character (probably using relationship)? Basically I need to achieve that next to a node with a given field I can also show data (separated by commas or otherwise) from the multi-value column from my custom table.

There is some excellent help about doing this with single-value columns at Using views with a custom table/schema but I guess it is a bit more tricky with multi-values.

Answer 1

You'll probably need 2 MySQL tables (to overcome the many-to-many relationship): one to link the nids to data-ids and one to link the data-ids to the actual data. One with three columns and the other with two: (primary key, nid, data-id) and (data-id, data).

    +------------+-----+--------+     +---------+--------+
    |nid-data-id | nid | data-id|     | data-id | data   |
    +------------+-----+--------+     +---------+--------+
    |1           | 1   | 1      |     | 1       | value  |
    |2           | 1   | 2      |     | 2       | value  |
    |3           | 2   | 1      |     +---------+--------+
    +------------+-----+--------+

This way you can use either multi-select or new values every time. Linking these tables can be done in the same manner as the article you provided (linking two can be found at Views - join tables or the views table API ) but I do not have any experience with that.