Of Hedgehogs and Database Design

  • Phil Factor

    SSC-Insane

    Points: 20115

    Comments posted to this topic are about the item Of Hedgehogs and Database Design

    Best wishes,
    Phil Factor
    Simple Talk

  • Eirikur Eiriksson

    SSC Guru

    Points: 182523

    He, he, like the Godzilla tables reference, starts like a small, nice, cuddly and sweet looking egg and ends up as a monster.

    😎

    Not certain where it comes from, but far too often one cannot do the job properly because of this strange resistance; deemed by the clients to be too complex or too expensive. Every time, without an exception, those clients have had to reiterate the work a few times, at every iteration comes at a much greater cost than doing it correctly in the first place.

    Without any prejudice, I don't think we are too clever for our jobs, but certainly, some are less than so for theirs.

  • Jeff Moden

    SSC Guru

    Points: 997309

    Eirikur Eiriksson wrote:

    ...at every iteration comes at a much greater cost than doing it correctly in the first place.

    True DAT!!!  What's amazing to me is how many people, even seriously senior people, never actually make that realization.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Eirikur Eiriksson

    SSC Guru

    Points: 182523

    Jeff Moden wrote:

    Eirikur Eiriksson wrote:

    ...at every iteration comes at a much greater cost than doing it correctly in the first place.

    True DAT!!!  What's amazing to me is how many people, even seriously senior people, never actually make that realization.

    Unfortunately, that is too true.

    😎

    Got a phone book size notes on such casualties, all in small print 😉

  • Stephane Faroult

    Newbie

    Points: 1

    I really enjoyed the comparison. One of the points that worry me most is that students are usually evaluated on whether they know the words and the notes. And sometimes you can sing even when you have forgotten the words (or never knew them in the first place). That's called talent, I guess.

    httpsref="https://www.youtube.com/watch?v=Zqd1ar5_7qw">Zqd1ar5_7qw</a>

  • kuopaz

    Ten Centuries

    Points: 1255

    Only enforcing uniqueness by an identity column PK is my No. 1. Simple, but see it all the time and the disastrous consequences.

  • Jeff Moden

    SSC Guru

    Points: 997309

    kuopaz wrote:

    Only enforcing uniqueness by an identity column PK is my No. 1. Simple, but see it all the time and the disastrous consequences.

    By the same token, it's also too true that people only enforce uniqueness by some awfully contrived "natural key" and then run into a hot form of hell when that natural key breaks due to a change and they have to update such keys in a bazillion other tables.

    Don't get me wrong... there are some very solid natural keys that follow the rules of being narrow, not null, unique, and immutable, but frequently not.

    With that I'll say that I frequently have both.  I'll frequently have an ID column as a PK and a natural key as an AK depending, of course, on the nature of the data, its use, and what the table is actually used for.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • kuopaz

    Ten Centuries

    Points: 1255

    Yes, I wasn't arguing for natural over surrogate keys, and tend to have both if possible. An ID for simplicity of joins and a unique index on other column(s). Hope this is a good rule of thumb.

    I worked on a database recently where the application had created millions of (real) duplicates before being noticed! Life would have been easier if an error had been thrown on the first one.

  • hmbacon

    Valued Member

    Points: 50

    It is my opinion that I was dismissed from a contract because I challenged the boss over a poorly designed database.  He used to be a developer/DBA on the project and didn't like his baby being called ugly.  In design meetings, one of the phrases I tried to suppress was, "Just create another row" (JCAR).  They were not using natural keys, just surrogates.  In the early days of new application, some errant code created over 700 duplicates in a very critical table and very few records were not duplicated at least 2 or three times.  Unfortunately, the end user could pick any one of the duplicates to create child tables.  As a result, they created mapping tables that tried to map duplicates to ease the pain.  In many cases this worked, but not all.  Hardware, indexes and partitioning helped.  We had one table with 200 columns that was a point in time history table.  Unfortunately this was not a true history table but a processing table that was growing really fast since they created another row for every set of updates.

    I have been designing databases for decades and am old school.  We  did conceptual modeling (Entity Relationship Diagrams) and used a tool to go from the conceptual model to the relational model and then generate the DDL.  It seems to me some leaders incorrectly use Agile to rationalize skipping design.  Databases were well behaved and easily extended.  It looks to me like many designers in today's IT shops are unaware of conceptual analysis and they start with "table" design based on the current page being created.  I would argue that most entities have fewer than a couple dozen attributes and natural keys are easy to identify.  "Table" design tends to put multiple entities in one table causing difficulty in identifying the natural key.  I am working on a series of articles on conceptual design that I plan to submit to SQL Server Central for publication as "Stairway to Conceptual Modelling."  Maybe some leaders will see the advantages of conceptual modeling.

  • Jeff Moden

    SSC Guru

    Points: 997309

    hmbacon wrote:

    It seems to me some leaders incorrectly use Agile to rationalize skipping design.

    And a whole lot more "SQL sins" like no documentation, little testing until it hits prod, nasty code that has no chance of performing well (from people that take Knuth's parable the wrong way), etc, etc.  I've also seen where people use DevOps to make excuses and then CI sometimes turns out to be absolutely the fastest way... to deploy bad code. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply