Re: Design question regarding arrays

On Sat  7 Aug 2004 22:03, A Gilmore wrote:

> 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);
>
> What would be a better method of doing this?

I think this is almost a classic problem of database design. Let's
see if I can get it right. =)

Create a separate table for your categories:

 category_names
  int id
  text name

And create a table which contain info about which items in my_table which
are related to which categories. I'm assuming you have a unique id in
the table 'my_table'

 book_category
 int my_table_id
 int category_names_id

Both of these of course are foreing keys from their respective tables.

This way, you can use a subquery to find all books of a category_name
which exist in the book_category, and then all the data about them in
my_table.

HTH, regards,
--
Fredrik Jonson