One-to-many WTF



  • So we have an application that has entities. These entities are similar, but there are different types. I can't really go into what entities without giving away what industry I'm working in. But most of them have multimedia of some kind, they can be pictures or audio or video or whatever. The WTF I will be describing has to do with the fact that each different type of entity, has different numbers of multimedia. One type might have a single photo, another might have no photo and twelve audio files, you get the idea.

    So a long-since-moved-on ex-coworker needed a way to store this into the application's MySQL database. So here's what he did.

    A Multimedia table, consisting of the following columns:

    • id (PK)
    • [other irrelevant stuff]

    A MultimediaCollection table, consisting of the following columns:

    • id (PK)
    • multimediacollection_id
    • multimedia_id (refers to the id in the Multimedia table, but without a FK constraint of course)
    • [other irrelevant stuff]

    Now picture me, trying to figure out from this godless mass of procedural PHP spaghetti, the idea behind these tables, when suddenly it hits me: the guy was trying to make a foreign key, but did it in reverse. He was trying to solve the problem of having a variable number of multimedia files in each collection. And instead of adding a foreign key to the Multimedia table, he decided that it would be a good idea to sort of have a "multimediacollection_id" number that he has to keep track of in his code, which sort of acts as a category number of some kind.

    Too good not to share.



  • INB4: I had considered the possibility that a Multimedia record might be reused among different MultimediaCollections. But no. Among thousands of Collections, not a single Multimedia belongs to more than one Collection. Also, the code makes sure this doesn't happen. It really is as I just described it: a reverse foreign key.



  • @toon said:

    So a long-since-moved-on ex-coworker
    @toon said:
    the guy was trying to make a foreign key, but did it in reverse.

    Is this long a years or a months thing? Cause if it was years then tracking him down and enacting revenge for this would be hard to pin on you.



  • You're looking at this the wrong way: this is an agile, scalable, NoSQL data store. This guy invented NoSQL before NoSQL was cool.



  • @morbiuswilters said:

    You're looking at this the wrong way: this is an agile, scalable, NoSQL data store. This guy invented NoSQL before NoSQL was cool.

    More like NoNoSQL.


  • Trolleybus Mechanic

    @realmerlyn said:

    @morbiuswilters said:
    You're looking at this the wrong way: this is an agile, scalable, NoSQL data store. This guy invented NoSQL before NoSQL was cool.

    More like NoNoSQL.

     

    NoMeansNoSQL


  • Considered Harmful

    @Lorne Kates said:

    @realmerlyn said:

    @morbiuswilters said:
    You're looking at this the wrong way: this is an agile, scalable, NoSQL data store. This guy invented NoSQL before NoSQL was cool.

    More like NoNoSQL.

     

    NoMeansNoSQL

    Rape DataBase Management System?



  • @joe.edwards said:

    @Lorne Kates said:

    @realmerlyn said:

    @morbiuswilters said:
    You're looking at this the wrong way: this is an agile, scalable, NoSQL data store. This guy invented NoSQL before NoSQL was cool.

    More like NoNoSQL.

     

    NoMeansNoSQL

    Rape DataBase Management System?



  • @morbiuswilters said:

    @joe.edwards said:
    @Lorne Kates said:

    @realmerlyn said:

    @morbiuswilters said:
    You're looking at this the wrong way: this is an agile, scalable, NoSQL data store. This guy invented NoSQL before NoSQL was cool.

    More like NoNoSQL.

     

    NoMeansNoSQL

    Rape DataBase Management System?

    Q: What do nine out of ten people enjoy?

    A: A gang rape.



  • @realmerlyn said:

    @morbiuswilters said:
    You're looking at this the wrong way: this is an agile, scalable, NoSQL data store. This guy invented NoSQL before NoSQL was cool.

    More like NoNoSQL.

    I invented a new technology called NonSQL! It's like SQL, but can't do anything SQL can do. Also, the only implementation is written in a single extremely complex INTERCAL expression.



  • @toon said:

    Q: What do nine out of ten people enjoy?

    A: A gang rape.

    locks door



  • @morbiuswilters said:

    @toon said:

    Q: What do nine out of ten people enjoy?

    A: A gang rape.

    locks door

    locks window






    unlocks window



    crawls out of morbs's house through window



    locks window



  • @Ben L. said:

    locks window






    unlocks window



    crawls out of morbs's house through window



    locks window

    Wait, my windows lock from the inside and the outside?



  • @morbiuswilters said:

    @toon said:

    Q: What do nine out of ten people enjoy?

    A: A gang rape.

    locks door


    dude, seriously? masturbating on a jokeabout gang rape? i mean, video, okay... but a joke?



  • @morbiuswilters said:

    You're looking at this the wrong way: this is an agile, scalable, NoSQL data store. This guy invented NoSQL before NoSQL was cool.
     

    Or he just wanted to implement a many-to-many relation, to be "future proof".



  • @toon said:

    Q: What do nine out of ten people enjoy?

    A: A gang rape.

    Deliberate pun ("many-to-one relation") or just coincidence?



  • @toon said:

    Q: What do nine out of ten people enjoy?

    A: A gang rape.

     


    Yeah, erectile dysfunction does indeed sap the joy out of many of life's pleasures for the 10% that suffer it.

     



  • Which one of those two tables has the metadata of the multimedia-file to include? I presume 'multimedia' represents a photo or video, and 'multimediacollection' represents a collection of 'multimedia'? Then he did exactly (well, a constraint would be nice...) the right thing: this way you can reuse a photo in different collection. If a photo points to a collection instead of the other way around a photo (or video) can be used only once.

    If you don't want to reuse collections should dispense of the 'multimediacollection'-table and add a 'entitimultimedia'-table: ID (PK), ENTITY_ID (FK to Entity.ID), MULTIMEDIA_ID (FK to Multimedia.ID). ID is not really needed here but can be included because of coding standards or a desire not to have a PK on multiple columns. If collections can be used more than once you add a MULTIMEDIACOLLECTION_ID (FK to MultimediaCollection.ID) field to the Entity-table.
    Well, if this were a new application anyway. At the moment you have to make due with what your predecessor wrought :P

    What I don't get is why MultimediaCollection has both an ID and a MultimediaCollection_ID...



  • @pnieuwkamp said:

    Which one of those two tables has the metadata of the multimedia-file to include? I presume 'multimedia' represents a photo or video, and 'multimediacollection' represents a collection of 'multimedia'? Then he did exactly (well, a constraint would be nice...) the right thing: this way you can reuse a photo in different collection. If a photo points to a collection instead of the other way around a photo (or video) can be used only once.

    The OP already pointed out that multimedia entries are never re-used.


Log in to reply