It's called a many-to-many relationship and you use a cross-reference table to represent the relationship. Say you have table
Searches ------------- SearchID FromDate ToDate etc... Users -------- UserID FirstName LastName Then to define what users are associated with what searches, you create a table Xref_Searches_Users -------------------------------- SearchID UserID Then if you want to find all searches for a user, for example, then you do SELECT Searches.* FROM Searches NATURAL JOIN Xref_Searches_Users WHERE UserID = @UserID If you're going to be searching both for searches by users and users by search, then you will likely want to create two indexes on the xref table, one on "SearchID, UserID" and another on "UserID, SearchID". HTH, Sam On Feb 13, 2008 8:00 PM, 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. > -- > <http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
