Can I (theoretically) use a Collection (e.g., Array, List) as a foreign key in a relational Database schema?

Question

Is is possible to use a Collection such as a Multiset or Array as the foreign key in a database scheme?

Background: A student proposes to use such a construct (don't use a join table for the n:m association) to store the following object structure in a database

public class Person {
    String name;
    List<Resource> res;
    …
}

public class Resource {
    int id;
    List<Person> prs;
    …
} 

SQL:2003

Answer 1

IMHO, the student didn't understand relational concepts. I don't know how collection types are implemented in todays databases, but they most probably store them in separate tables.

Edit If it would be technically possible, I doubt that it would be useful. Consider the query language. Sql is designed for relational structures, I doubt that you could really have the same flexibility and possibilities using collection types. If you had it, you couldn't read it anymore. Consider indexes. etc. etc.

Relational structures are primitive, but very powerful and fast. You can do (almost) everything with them. Collection type are actually not needed, although they may be useful in certain cases. Using collections (for relational stuff) just would be more complex and less pure.

Answer 2

Simply put, I would have said no. I don't think that it is possible in SQL2003 and in any case it would couple the code and the database structure too closely. Remember good practice of structuring code so that a change to your database doesn't require a change to your code and vice versa.

As Stefan said you need separate tables for Resource and Person with Foreign Key links to the indexes between them.

So based on the classes shown each table would need 3 coloumns.

You would then obtain your class data by using an appropriate query to the database.

Answer 3

In principle, yes you can implement such a referential constraint. That's assuming your RDBMS allows a suitable type for the set of values. For instance it could be a relation value if relation-valued attributes (RVA) are supported.

If it was a RVA then the constraint could easily be expressed in the relational algebra / calculus or its equivalent. For instance you can do it in a RDBMS like Rel which supports the Tutorial D language. Doing it in SQL is probably going to be a lot harder - but then SQL is not a real relational language.

Of course, the fact that you can do it relationally does not necessarily make it a good idea...

Answer 4

As David pointed out, theory allows attribute values to be of a collection type.

However, in your case, which is just to model n:m relationships (am I right about that), it simply does not apply.

If a Person P1 has associated resources R1 and R2, the row for this person would be like {P1, {R1, R2}}. If that collection-typed column were a foreign key referencing some other table, it would mean that there had to be another table in which a row appeared with the collection value {R1, R2} in some column. Which table would that be in your example ?

Collection-typed attributes are mostly useful if you have a need for dealing with empty collections alongside non-empty ones. There is no relational join in the world that will do its equivalent for you.