Three dimensional database table

Question

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?

Centennial

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? – Classics

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? – Centennial

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. – Classics

Coming back to this eight years later, Datomic supports both collections (maps, lists, and vectors) and cardinality many for fields. – Centennial

Answer 1

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.

Resound

Answer 2

The standard many-to-many approach, many users to many profile pictures, is easily covered by the three table approach:

Table: Users
Table: Pictures
Table: User_Pictures

However, if you move to a NoSQL approach, you can store a User document (usually in JSON format), that stores an array of profile pictures for that user in a single table.

@gordy +1 for the Oracle link. I wasn't sure if any RDBS supposed arrays.

Fleischer

Answer 3

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.

Grabble

I didn't suggest multiple columns. – Centennial