Re: [sqlite] Multi-valued attributes

[Samuel Neff]

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.
> --
> 
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-valued attributes

[P Kishor]

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

Re: [sqlite] Multi-valued attributes

[P Kishor]

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

Re: [sqlite] Multi-valued attributes

[Darren Duncan]

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

[sqlite] Multi-valued attributes

[gongchengshi]

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.  
-- 
View this message in context: 
http://www.nabble.com/Multi-valued-attributes-tp15471820p15471820.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users