At 5:00 PM -0800 2/13/08, gongchengshi 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.
Some quasi-relational DBMSs (and all truly relational DBMSs) support actual multi-valued attributes/fields, which in the general case are relation/rowset valued, or in the less general case are specifically set or array etc valued; PostgreSQL supports the latter to some extent. AFAIK, SQLite does not support multi-valued fields, and so with it your schema will have to be of the form you get when you split the prior relvar/table with each multi-valued attribute/field separated into its own relvar/table (every new table also has a copy of the original table's primary key attribute), and subsequently those extra relvars/tables are relational-ungrouped to turn each multi-valued-field tuple/row into multiple tuples/rows. You are then creating what are commonly called intersection tables, afaik, which are common when implementing many-to-many relationships between tables. -- Darren Duncan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
