This one is difficult to summarize in the title.
I have a set of data such that a single table can contain all information in a single row per item except for one column. That column will contain a URL or many URLs. So I have three ideas:
- Add another table to store all URLs with a foreign key back to the main table for each
- Use a single table and match the URLs with a regular expression
- Use a single table and store the URLs in a
jsonbcolumn as an array
For case #2, I would be storing the regular expression in the column and matching values against it, e.g.:
select * from foo where 'http://someting.example.com/foo' ~ url
url column has a row with the value:
Let's assume the row set for
foo would number in the 100s (1000 would be a very large set), but most likely in the 10s. Let's further assume that the URL count could be double or triple in number.
Would the performance of each of these three possible designs be inline with each other? In other words, would the choice of design be a matter of preference? Or would designs #2 and #3 be so much worse than #1 that it would never be a good idea to choose them over #1?
I need to match rows by the URL. But I'd also like to keep maintenance to a minimum.