Hello,
Ill assume I have the correct mailing list since my question regards the
tip located in the 7.4 user docs on arrays. It says that searching for
specific array elements can be a sign of bad design. To fully quote the
documentation :
"Tip: Arrays are not sets; searching for specific array elements may be
a sign of database misdesign. Consider using a separate table with a row
for each item that would be an array element. This will be easier to
search, and is likely to scale up better to large numbers of elements."
I was considering using an array to hold catagorization information in
my db. So to use say books as an example, Id have a record for each
book and a text array column contain the list of catagories it belonged
to, such as:
{"sci-fi", "horror", "suspense"}
Regularly Ill have to search this db for records containing a specific
catagory. For example get all books belonging to the sci-fi catagory,
for the most parts the searchs will only look for a single catagory.
They would look like this :
SELECT * FROM my_table WHERE 'sci-fi' = ANY (catagory);
Is that bad design? What would be a better method of doing this? The
number of catagories will number around 50, maybe more, and change
periodically, so creating individual BOOLEAN columns for each catagory
would be tedious and undesirable. Although there are many different
catagories, rarely will more then 1-5 apply to a single record, so each
array wont be very large.
I don't know how large this database will grow, I would like to design
it to scale well just to be safe.
Thanks in advance for any help.
A Gilmore