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.

[Darren Duncan]

Post by 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.

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

[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

Post by 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.
--
<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>

[P Kishor]

Post by 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.
--

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.

[P Kishor]

Replying to my question, to add a bit of information specific to my
current situation. Please see below --

Post by P Kishor

Post by 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.
--

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)