• (nodebb)

    It was a tricky problem to crack, but the product owner's developers had come up with a novel solution to resolve it:

    Actually we could add a really unique id instead which would never get repeated, even across customers, it would stay the same for the entity and never be reused

    And thus, the vendor invented primary keys and unique identifiers. Unfortunately, the vendor was not E.F. Codd, the year was not 1970, primary keys had been invented already, and in fact were well understood and widely used. But not, apparently, by this vendor.

    (Hoping the formatting above will work correctly.)

    If the vendor had waited until then to realise this was an actual possibility, then it's highly probable that the internal details of the solution will not involve automatic ID fields and uniqueness constraints, you know, the stuff the database provides to solve this very problem...

    Oh, and that in a few years, those internal details will end up posted here, by someone...

    Addendum 2025-10-08 07:04: (Yay! It worked the way I wanted.)

  • (nodebb)

    They didn't necessarily need to "add a really unique id". There wouldn't be anything wrong with using the combination of customer ID and entity ID as the primary key. They would just have to know that they can do that.

  • Hanzito (unregistered) in reply to Dragnslcr

    You guys have composite keys? Is it 1971 already?

  • lzsiga (unregistered)

    Off: in some famous database-product, session-identifiers (SIDs) are reused (though sessions also have unique serial-numbers), and in some version you could inherit not only the SID from a previous session, but the statement-cache also, so sometimes the statement-parsing didn't happened, as the statement (and its parsed form) was already in the statement-cache.

    The problem came when the statement included an unqualified table-name (such as insert (cnt,txt) values (user_log_seq.nextval,v_msg) into user_log when the old session and the new session belonged to different users: the execution tried to insert newuser's record into olduser's table.

  • (nodebb)

    Until they find out all the problems with their home-rolled "really unique identifiers". Then you can stand by for the rollout of the "really, really unique identifiers".

    Hopefully by then, you will have switched to a vendor with at least half a clue.

  • Peter of the Norse (unregistered) in reply to Dragnslcr

    I worked at a start up that had this problem for a while. Each client had its own DB, but there was a shared DB because of third party product with a soft foreign key to one of the client PKs. This wasn’t an issue 99% of the time because we used a multi-master DB where the PKs went up about 10 instead of 1, and not every client user used the third party product. (Sorry for being vague. They only care about the NDA when we’re talking about mistakes.)

    We eventually fixed it by changing the soft foreign key to something like 1000007654 for client 1, but it was a weird thing that was hard to pin down because it was so hard to reproduce.

Leave a comment on “A Unique Mistake”

Log In or post as a guest

Replying to comment #685174:

Log Out

« Return to Article