CODEWITHSUNDEEP
sqldatabasearraysdimensions
Dennis Hackethal
Dennis Hackethal

Reputation: 14295

Three dimensional database table

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?

Upvotes: 13

Views: 17992

Answers (3)

dkretz
dkretz

Reputation: 37655

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.

Upvotes: 3

Adrian J. Moreno
Adrian J. Moreno

Reputation: 14859

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.

Upvotes: 8

gordy
gordy

Reputation: 9796

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.

Upvotes: 11

Related Questions