3D database table

Question

We were all there - consider the following example: firstly, the client says that “each user will have only one profile image,” so we add a field for this to the user table - six months later, the requirements change and the user really needs to have n images profile.

Now this is only possible if you add a new table, such as user_pictures, to handle the new power 1: n instead of 1: 1. This can often be very difficult. Whenever I encounter this problem, I wonder why we are not using all three dimensions that we can think of. The two-dimensional table is limited in such a way that it is somewhat incomplete - what if, referring to our problem with the profile image, again, the image field in the users table had depth , and this depth made the field an array that perfectly displayed both 1: 1 and 1 powers: n

The fields of the table will simply become arrays and automatically support both powers - wouldn't that be something? At least I would use it. Is there something like this out there already?

Answer 1

Oracle supports arrays as well as nested tables . They seem to fit your requirements. These days, although people prefer to model everything as tables and relationships in order to keep things simple and consistent, that’s why modern RDBMSs usually don’t support this stuff, and I don’t believe it ever got into standard SQL.

Answer 2

The standard many-to-many approach, many users to many profile images, is easily covered using three tabular approaches:

Table: Users
Table: Photos
Table: User_Pictures

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

@gordy +1 for Oracle link. I was not sure what any RDBS intended arrays were.

Answer 3

You describe the denormalization method (multiple columns for instances of the same field), and this usually leads to tears if you do not fully understand the consequences of violating basic relational principles.

A classic difficulty arises when you want to query in a field ("find a user who has this image"), and you find that the SQL statement with "AND picture IN (pic1, pic2, pic3)" cannot be indexed, and your the optimizer begins to plan his revenge.