August 22, 2020 at 5:00 am
Comments posted to this topic are about the item Of Hedgehogs and Database Design
Best wishes,
Phil Factor
August 22, 2020 at 1:17 pm
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.
August 22, 2020 at 6:49 pm
...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
Change is inevitable... Change for the better is not.
August 23, 2020 at 8:19 am
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 😉
August 23, 2020 at 12:44 pm
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>
August 23, 2020 at 1:08 pm
Only enforcing uniqueness by an identity column PK is my No. 1. Simple, but see it all the time and the disastrous consequences.
August 23, 2020 at 6:55 pm
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
Change is inevitable... Change for the better is not.
August 23, 2020 at 8:04 pm
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.
August 26, 2020 at 7:11 pm
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.
August 26, 2020 at 10:12 pm
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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply