On 2/13/08, gongchengshi <[EMAIL PROTECTED]> wrote: > > I have a table were each row needs to be able to store a list of entries from > another table as one of its attributes. For instance the table is a > collection of search filters. The filters table has attributes: FromDate, > ToDate, Users, Devices. The Users attribute is not a single value but a > list of Users contained in the Users table. Same with the Devices > attribute. The Devices attribute is actually a list of Devices in the > Devices table. > > How do you go about defining this schema in SQL? The book I am reading > "Database Systems" by Connolly and Begg say that you can have such > relationships but they don't say how to create them. I am using sqlite as > my DBMS. > --
Variations on this question have been discussed before, and both are covered adequately by Darren Duncan's reply (multi-valued columns a la PostgreSQL's variable-length multidimensional arrays) [1], or Sam Neff's suggestion of building a many-to-many cross-reference table, a common solution in most such situations. [1] http://www.postgresql.org/docs/8.3/static/arrays.html I like the idea of a multidimensional array as it seems intuitively a lot less complicated than the alternative, especially when many just many-to-many relationships are involved. Even though SQLite doesn't have the array datatype support, it can be easily accomplished in the application (assuming there is a programming language and application environment makes this easy). For me, it seems fast enough. SQLite does the really rapid searching and locating the data, made all the more rapid because of the lack of any multi-table JOINs required, and once I have the data, and I can happily split it into separate values and do another lookup. Yes, multiple lookups, and I have to balance the pros and cons and benchmark my own situation. So, while I will benchmark my own situation, I just wanted to find out if there are any other gotchas that I need to be aware of if I decide to implement a table design which holds a list of lookups rather than a cross-reference table. Many thanks, Puneet. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
