Database Design Party



  • I'm working with an "entrprise" POS system from a big 3 letter company and have been looking at their MySQL database design today.  Specifically a table called "PARTY".

    The PARTY table has one column:  PARTY_ID (decimal(20,0)) PRI

    The table contains numbers, from 1 to ~20 million.   It's used to store primary keys for other tables. For instance, the ADDRESS table, doesn't have an ADDRESS_ID column, it has a PARTY_ID column.  The CUSTOMERS table doesn't have a CUSTOMER_ID, it too has a PARTY_ID column.  A VENDOR_ID column in the VENDORS table?  No, it's at the PARTY too.

    The developers said this is "for O-R related purposes".

    The PARTY table doesn't auto-increment; there are no triggers or stored procs to manage the PARTY.  It's done somewhere in the code (or maybe the O-R mappings, nobody seems to know exactly). 

    Maybe this is the design pattern of the future. Or the past.  Or some alternative universe.  I dunno. 



  •  So can an address and a customer have the same PARTY_ID, or are they globally unique?  Or is it the other way around that everything with the same PARTY_ID is part of the same entity (party?)?

    If they are globally unique, I'd like to know why since there that would be a WTF in its own right (though in this case they should be using auto-increment, and not using it is another WTF).

    If multiple other tables can have the same PARTY_ID, but that doesn't mean correlation then there's no reason to have the PARTY table with its PARTY_IDs and each table would have its own unique identifier.

    If anything with the same PARTY_ID is part of the same entity it at least makes sense - a party refering to an entity with all of its constituent parts (though you lose the ability to use the PARTY_ID as a unique key if you need to introduce any kind of -to-many relationships).  Of course even then each table should have its own ID and foreign keys properly identified and established accordingly (so that while a customer has an address and a vendor has an address a customer can't be associated with a vendor unless such a relation is explicitly established)



  • @airdrik said:

     So can an address and a customer have the same PARTY_ID, or are they globally unique?  Or is it the other way around that everything with the same PARTY_ID is part of the same entity (party?)?

    If they are globally unique, I'd like to know why since there that would be a WTF in its own right (though in this case they should be using auto-increment, and not using it is another WTF).

    If multiple other tables can have the same PARTY_ID, but that doesn't mean correlation then there's no reason to have the PARTY table with its PARTY_IDs and each table would have its own unique identifier.

    If anything with the same PARTY_ID is part of the same entity it at least makes sense - a party refering to an entity with all of its constituent parts (though you lose the ability to use the PARTY_ID as a unique key if you need to introduce any kind of -to-many relationships).  Of course even then each table should have its own ID and foreign keys properly identified and established accordingly (so that while a customer has an address and a vendor has an address a customer can't be associated with a vendor unless such a relation is explicitly established)

     

     

     

    You would think they would just use a primary key unique to the table, right?  But each party_id value in each table is unique because it is pulled from the party table.  So the party_id in the customer table will never be the same as a party_id in the vendor table.  Primary keys are unique across all tables.  :/  

     

     



  • @lizardfoot said:

    Primary keys are unique across all tables.  :/  

    Holy crapoly - that's awesome!



  • I have seen this before, and it was "to support some ORM thing" so their explanation is familiar, however it does not make sense. "DB keys must be unique across the whole universe" is not an easy requirement to work with. It works great in testing, but when you have any amount of concurrent users, the whole thing breaks down, unless you create an "ID server" as a singleton, or something like that... which puts a major bottleneck in the system. I've seen this work successfully but it leads to a bunch of problems down the road. It's just another symptom of not having good DB developers. I currently make a lot of money cleaning up after that mess, and have done so for many years... so it gives me great pleasure to know that the stupid is still out there :)



  • @lizardfoot said:

    Primary keys are unique across all tables.

    This is why God created GUIDs.



  • @Speakerphone Dude said:

    This is why God created GUIDs.

    ... and then Zardoz created God and in the end Zardoz re-created man.



  • @blakeyrat said:

    @Speakerphone Dude said:
    This is why God created GUIDs.

    ... and then Zardoz created God and in the end Zardoz re-created man.

    You can't blame people if they see you differently knowing that you worship Zardoz

    Eeew


  • Hey Zardoz has some cool science fiction concepts.



  • @blakeyrat said:

    Hey Zardoz has some cool science fiction concepts.
    Not the least fictiony of which is Sean Connery dressed in a little number from the Aeon-Flux-Meets-Lara-Croft-And-Vampirella collection.



  • @Speakerphone Dude said:

    [Eeew
    Why is James Bond wearing a diaper?

     According to IMDB, Director John Boorman made a cameo appearance in the movie as a slave who gets shot by Sean Connery and the wadding from the blank got embedded in his forehead.

     



  •  Is this their way of assigning unique ids to their ORM objects? Because I'm pretty sure I could come up with a scheme that doesn't involve turning the database into something that looks like it teleported along with a fly.



  •  What is an O-R?

     

    One-Reallymany relationship?



  • @DOA said:

     Is this their way of assigning unique ids to their ORM objects? Because I'm pretty sure I could come up with a scheme that doesn't involve turning the database into something that looks like it teleported along with a fly.

     

    I don't think this design goes far enough. You're not enterprisey until you store datetimes as ticks and implement FK relationships as a comma-separated Id values in a text column in the PK table. And why no xml?

     



  • @jasmine2501 said:

    "DB keys must be unique across the whole universe" is not an easy requirement to work with. It works great in testing, but when you have any amount of concurrent users, the whole thing breaks down, unless you create an "ID server" as a singleton, or something like that... which puts a major bottleneck in the system.
     

    What's wrong with having a sequence that increments by 1e6, and each server has its own startpoint? Ie. server 1 starts at 1, server 2 starts at 2, etc.Then only the initial "server IDs" have to be centrally issued.

    If 1e6 servers isn't enough for you, then increment by 1e9 - and if you want to be future-proof, just reserve the latter half of server IDs, and if you reach it, make the increment ten times the current value - then you've got a nice, new range to give numbers ...

    Or, if you've got a nice database that allows you to store complex numbers, how about having the real value taken from a per-server sequence, and each server gets its own imaginary part? Etc. and so on and on and on ...

    Perhaps I'm mistaken, but it doesn't sound that complicated to me.


  • Discourse touched me in a no-no place

    @flop said:

    What's wrong with having a sequence that increments by 1e6
    This: @flop said:
    Then only the initial "server IDs" have to be centrally issued.
    It would never work/happen/get configured right. Then there's what happened/happening when they did this with IP addresses (v4.)



  • It sounds like you need to get some Joe Celko or Louis Davidson books and BEAT PEOPLE INTO UNCONSCIOUSNESS WITH THEM.


  • Trolleybus Mechanic

    @blakeyrat said:

    Hey Zardoz has some cool science fiction concepts.

     

    Zardoz: Reading backwards for survival since 2293!

     



  • When they started making mobile phone-sized projectors, people were all like "Star Wars!" I was like, "fuck Star Wars, that shit is Zardoz!" Then I got a prize.



  • Eighth-normal form (8NF, a.k.a. "The Ocho"): a level of database normalization in which the keys are stored in a central table.



  • @flop said:

    What's wrong with having a sequence that increments by 1e6, and each server has its own startpoint? Ie. server 1 starts at 1, server 2 starts at 2, etc.Then only the initial "server IDs" have to be centrally issued.

    If 1e6 servers isn't enough for you, then increment by 1e9 - and if you want to be future-proof, just reserve the latter half of server IDs, and if you reach it, make the increment ten times the current value - then you've got a nice, new range to give numbers ...

    This is wrong at 1e9 levels. Vou've obviously never implemented anything remotely related to a relational database.

    Or, if you've got a nice database that allows you to store complex numbers, how about having the real value taken from a per-server sequence, and each server gets its own imaginary part? Etc. and so on and on and on ...
     

    So you've basically got a pair of numbers, one of which identifies the sequence. Which is easier when stored in the table, and which they didn't want to do in the first place.

    But forget the complex numbers. We're not going to calculate with ids.

    ...

    [TURNS PALE]



  • @dhromed said:

     What is an O-R?

     

    One-Reallymany relationship?

    Onion-Rings.

     



  • @RichP said:

    Eighth-normal form (8NF, a.k.a. "The Ocho"): a level of database normalization in which the keys are stored in a central table.

    One time I had a client who was using "The Ocho" for their home-made Enterprise Application. Well, it was not an architecture decision, more of a side-effect of their database design which had ONE table (with lots and lots of columns). To add to the WTF, the RDBMS was DB2 and the single table was called "Oracle". At first I was thinking they created it on Oracle and moved to DB2, but no, it was the previous name of the Enterprise Application because it was designed as a decision-support system (can't recall the new name but it was nothing special).

    That client got wiped in the 2008 CDO collapse, it's too bad because they were a high-paying client and the reception girl was pretty cute.



  • @TGV said:

    (1 + i) * (1 - i) = 0

    Dude, that's 2.



  • @dargor17 said:

    @TGV said:
    (1 + i) * (1 - i) = 0
    Dude, that's 2.
     

    I knew there was something wrong with the whole idea...



  • @TGV said:

    I knew there was something wrong with the whole idea...
     

    Perhaps you just imagined it to be correct.



  • @Cassidy said:

    @TGV said:

    I knew there was something wrong with the whole idea...
     

    Perhaps you just imagined it to be correct.

     

    There should be some kind of ROFLOLZOR award for that, which would be a charred, scratched, chipped and bleached wooden figurine of a trollface or summat.

     


Log in to reply