Replying to my question, to add a bit of information specific to my current situation. Please see below --
On 2/13/08, P Kishor <[EMAIL PROTECTED]> wrote: > 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. > In my case, I have one attribute that has a many-to-many lookup with two different kinds of attributes. I am tracking three attributes -- people, agencies, and reports. Both people and agencies have a many-to-many relationship with reports. I have the following tables persons(person_id, ..) agencies(agency_id, ..) reports(report_id, ..) alternative 1: two xref tables -- persons_x_reports(person_id, report_id) agencies_x_reports(agency_id, report_id) alternative 2: one xref table foo_x_reports (foo_id, report_id, foo_type[person|agency]) alternative 3: list of reports persons(person_id, list_report_id) agencies(agency_id, list_report_id) _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
