We have all been there - consider the following example - first, the client says "every user shall only have one profile picture", so we add a field for that to the users table - half a year later, requirements change and a user actually needs to have n profile pictures.

Now, this seems only possible if you add a new table such as user_pictures to handle the new cardinality 1:n instead of 1:1. Oftentimes this can get very complicated. Whenever I come across this problem, I wonder why we don't use all three dimensions that we can think in. A two dimensional table is limited in a way that it is somewhat incomplete - what if, referring to our problem with the profile picture again, the picture field in the users table had a depth, and that depth made the field an array that perfectly represented both cardinalities 1:1 and 1:n at the same time.

Table fields would simply become arrays and automatically support both cardinalities - wouldn't that be something? At least I would use it. Is there something like it out there already?

All the possible relationships between "things" in a database (one-to-one, one-to-many, many-to-many), are covered. What would be a use case for a "three-dimensional" table - whatever that means?
–
NullUserExceptionDec 14 '12 at 23:49

An example use case is the one above with the profile pictures: You would need to add a new table, but that is clearly cumbersome. Model relationships would need to be changed, queries updated accordingly, etc - involving lots of pain and work. I think we are able to do better than that. Wouldn't a two dimensional field, making the table three dimensional, solve that?
–
CharlesDec 14 '12 at 23:57

1

Relational databases are supported by a very solid foundation - relational algebra. It ensures they are both fast and correct. That's one of the reasons why they are still around when other kinds of databases came and went over the years. Don't fix what isn't broken.
–
NullUserExceptionDec 15 '12 at 0:02

3 Answers
3

Oracle has support for arrays as well as nested tables. Either seem to fit your requirements. These days though people prefer to model everything as tables and relationships to keep things simple and consistent and so modern RDBMSes don't generally support this stuff and I don't believe it ever made it into standard SQL either.

You are describing a denormalization technique (multiple columns for instances of one field) and it usually leads to tears unless you thoroughly understand the consequences of violating basic relational principles.

A classic difficulty comes when you want to query on the field ("find the user who has this picture") and you discover that an SQL statement with "AND picture IN (pic1, pic2, pic3)" can't be indexed and your optimizer starts planning its revenge.