Using array VS separate table JOIN for many to many relationship

Question

I have a use case where I need to create entity in postgresql to store some configurations. Example of two rows for configuration is below:

ConfigurationCode	OperationOne	OperationOneValue	OperationTwo	OperationTwoValue
ConfigurationCode1	INCLUDE	{'1,2,3,4,5'}	EXCLUDE	{'Name1, Name2, Name3'}
ConfigurationCode2	ALL	{}	INCLUDE	{'Name1, Name2, Name3'}

Here, if I want to fetch configuration where operationOneValue is 1 and operationTwoValue is Name4 I should get ConfigurationCode1 since it "includes" 1 and doesn't "exclude" Name4. I hope you got the use case.

What the correct way of creating entity for such requirement? Using Arrays or Using Separate Table.

I have used arrays to store OperationOneValue and OperationTwoValue and use below query:

SELECT * FROM my_table_name
WHERE
(OperationOne = 'ALL' OR (OperationOne = 'INCLUDE' AND 'value' = AND(OperationOneValue)) OR (OperationOne = 'EXCLUDE' AND NOT 'value' = ANY(OperationOneValue)))
AND
(OperationTwo = 'ALL' OR (OperationTwo = 'INCLUDE' AND 'value' = AND(OperationTwoValue)) OR (OperationTwo = 'EXCLUDE' AND NOT 'value' = ANY(OperationTwoValue)))

The data in each array column can go upto 10k. And number of rows in table can upto 1k. What are advantages and dis-advantages of both approach?

Answer 1

A separate table (in the case of a many-to-many cardinality relationship, it would be a one-to-many-to-one bridge table) is generally the better way to go. It is a more normalized approach. These are some of the benefits:

• Improved performance when querying by those attributes
• Better data management when the value of an attribute changes. I.e. you wouldn't need to update anything but the single parent value when referential integrity is implemented by the keys of that data
• Improved data integrity for the same reasons as above
• Improved queryability when needing to operate on the data of those attributes, such as when it's numerical or date data