Imagine a simple one-to-one relation whenever a second object always exists. For instance a user and his default virtual folder, whenever a user is created, the user’s default folder should also be created, and it cannot be deleted.
First of all a pure o-t-o relation is usually pretty useless, just add what’s needed to the table schema and skip the foreign relation part. But let’s say we have one-to-many or many-to-many foreign relations and somewhere in this otm and mtm crowd there is our ‘soulmate’ for which we could say he is the only one or should I say the only one-to-one :)?
How could be this one-to-one connection established properly?
Let’s look closer at the first example with users and folders, and let’s say schema connects one user to many folders. Usually this done via a user_id colum added to the folders table. So how we could establish the ‘soulmateness’ over this schema?
The simpliest way possible: add some kind of a ‘soulmate’ flag column, and set it to true for the corresponding folder.
Here is how every user can obtain his soulmate folder:
SELECT * FROM folders WHERE user_id = my_id AND soulmate;The more folders users will have on an average, the longer search would become, more iops would be wasted e.t.c.
We can definitely sky-rocket it with +1 partial index on the user_id column of the folders table with a ‘soulmate = TRUE’ condition. But even a blazing fast search will still require +1 DB query to get users soulmate folder id whenever needed and also we added +1 index size of a users table to our schema.
The reverse id way: add soulmate_folder_id to the users table and fill it with the soulmate folder id right after its creation.
Now a freshly obtained record of myself will reveal my ‘the only one’ folder id, hence I could put something in it without even querying the full folder record.
Still whenever system has a random folder record it needs a soulmate flag column on the folder record to get the idea is this a ‘just a folder’ or is it ‘the one’ folder for some user in the system. And we still need to add +1 index to find the corresponding user for the ‘someones special’ folders.
The unusual way: let them have same PK id! But hold on, sooner or later the amount of folders in the system will outrun the amount of users in it and this would became impossible!
Yes, unless we just shift the sequence id for the folders table as far as reasonable, for instance if the pkeys are 8-bytes ints we could shift sequence by 10 billions, than adding every human in the wolrd will still not be able to fill it completely.
SELECT setval( sequence_name, 10_000_000_000::bigint );So the only thing left is to set our soulmate folder pk id equal to user pk id during its creation, and that’s it.
If folder pk_id is less than shift, then its a soulmate folder for the user with same id, if the folder pk_id is bigger than shift, then its a usual folder. We don’t need to add any new indexes for any of this tables, cause we already should have the pkey indexes in place for both of them.
The only problem with this approach — you can only go for it at the very beginning. Aligning an existing schema to this approach looks nearly impossible to me. Well nothing is ideal in the world, unless you at least started it like that.
