Design question regarding arrays
От | A Gilmore |
---|---|
Тема | Design question regarding arrays |
Дата | |
Msg-id | 4115B43F.1060302@shaw.ca обсуждение исходный текст |
Ответы |
Re: Design question regarding arrays
Re: Design question regarding arrays |
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: