Your Biggest Data Model Complaints

Question

Your Biggest Data Model Complaints

Steve Jones - SSC Editor

SSC Guru

Points: 737236
More actions
February 28, 2025 at 12:00 am

#4537112

Comments posted to this topic are about the item Your Biggest Data Model Complaints

Viewing 13 posts - 1 through 12 (of 12 total)

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

David.Poole SSC Guru Points: 76105 More actions · Answer 1

I insisted on attaching meaningful business comments to DB objects and had a nightly process that generated DB Documentation website using a competitor to Redgate SQLDoc. This was disparaged as "Dave Poole's hobby". That is my "old man shouts at clouds" trigger, especially as this is something that, these days, data Catalogs, data governance and Agentic AI all make use of .

I loathe people using reserved words for the names of things. It is easy to write a test to detect it. Mind you, Devs are very good at disabling tests that are inconvenient to them. That was not a joyful experience. "It broke the build", "No it didn't, you added something that crashed through the guardrails".

Document DBs

Stuffed with documents representing some form of God Object.
With collections (tables) that store many types of document.
If data modelling were sculpture then documents that look like someone sat on a crude high school thumb pot rather than something like The Veiled Virgin.

For me its the accumulation of small niggles that bug me rather than the grosser errors.

LinkedIn Profile

Thomas Franz Hall of Fame Points: 3915 More actions · Answer 2

Allowing no NULL in valid_from / valid_to seems to be a good idea. But implement it with a DEFAULT = '18991231' on BOTH columns (as at one of my previous employers) is a very bad idea because you always have to query it by

SELECT * from tbl as t WHERE t.valid_from <= GETDATE() AND (t.valid_to >= GETDATE() OR t.valid_to = '18991231')

Very annoying when you have to write it thousands of times in your daily queries / whole project code base.

At my current employer we use 20991231 as DEFAULT for valid_to, so that my grandkids (or an AI) may have some little fun with the inherited Y2,1K problem 👿(okay, it is not that big deal to search all code for 20991231 and replace it with 21991231)

God is real, unless declared integer.

Robert Frasca SSCertifiable Points: 6027 More actions · Answer 3

I had a client that brought my consulting firm in to help crank out reports for their transactional system. It was a rudimentary intranet social media app. They had created an object oriented data model where every object was based on an entry in their Objects and ObjectType tables. As they slowly rolled out the product to customers they couldn’t understand why the performance of the product was suddenly terrible as they added users. I gently tried to point out that the data model was causing extensive locking and blocking problems. It didn’t help when you added in the reporting features. We were required to use nested CTE’s as dictated by the client in every report. I don’t remember how it ended, but the client refused to look at the data model as the problem because their lead developer implied that we were the problem. I never heard anything else about that company ever again.

"Beliefs" get in the way of learning.

david.gugg SSCertifiable Points: 6123 More actions · Answer 4

In my first job as DBA we had a database where a separate schema was generated for each customer. So you'd have [customerid1].Orders, [customerid2].Orders, etc. The database had thousands of objects. I was too new to really dig into it, but I suspect it was a huge hassle to manage and maintain.

The OLTP design style I loathe the most but see fairly often is where the table is designed to handle any number of possible values by creating as many columns as they might ever need. For example:

CREATE TABLE nonsense
(
ID INT IDENTITY
, objectname VARCHAR(200)
, objectattribute1 CHAR(1000) NOT NULL
, objectattribute2 CHAR(1000) NOT NULL
, ...
, objectattribute100 CHAR(1000) NOT NULL
);

Be still, and know that I am God - Psalm 46:10

Audionova Old Hand Points: 305 More actions · Answer 5

Steve Jones - SSC Editor wrote:

Maybe there's another reason (let me know if there is one).

I've seen people do bad design not out of maliciousness, but because they are confidently incorrect. They honestly think that the way the designed it, problems and all, was the best way to do it.

Ignorance is bliss I guess.

Steve Jones - SSC Editor SSC Guru Points: 737236 More actions · Answer 6

David.Poole wrote:

I insisted on attaching meaningful business comments to DB objects and had a nightly process that generated DB Documentation website using a competitor to Redgate SQLDoc. This was disparaged as "Dave Poole's hobby". That is my "old man shouts at clouds" trigger, especially as this is something that, these days, data Catalogs, data governance and Agentic AI all make use of .
I loathe people using reserved words for the names of things. It is easy to write a test to detect it. Mind you, Devs are very good at disabling tests that are inconvenient to them. That was not a joyful experience. "It broke the build", "No it didn't, you added something that crashed through the guardrails".
Document DBs
Stuffed with documents representing some form of God Object.
With collections (tables) that store many types of document.
If data modelling were sculpture then documents that look like someone sat on a crude high school thumb pot rather than something like The Veiled Virgin.
For me its the accumulation of small niggles that bug me rather than the grosser errors.

Lol, I hate reserved words as names. That being said, I wish "name" weren't reserved. 😉

Steve Jones - SSC Editor SSC Guru Points: 737236 More actions · Answer 7

Thomas Franz wrote:

Allowing no NULL in valid_from / valid_to seems to be a good idea. But implement it with a DEFAULT = '18991231' on BOTH columns (as at one of my previous employers) is a very bad idea because you always have to query it by
SELECT * from tbl as t WHERE t.valid_from <= GETDATE() AND (t.valid_to >= GETDATE() OR t.valid_to = '18991231')
Very annoying when you have to write it thousands of times in your daily queries / whole project code base.
At my current employer we use 20991231 as DEFAULT for valid_to, so that my grandkids (or an AI) may have some little fun with the inherited Y2,1K problem 👿(okay, it is not that big deal to search all code for 20991231 and replace it with 21991231)

I know a little "prompt" tool that could help here ;), that being said, I hate magic numbers more than nulls.

Steve Jones - SSC Editor SSC Guru Points: 737236 More actions · Answer 8

Robert Frasca wrote:

I had a client that brought my consulting firm in to help crank out reports for their transactional system. It was a rudimentary intranet social media app. They had created an object oriented data model where every object was based on an entry in their Objects and ObjectType tables. ...

The OOP dbs work great for sticking stuff in, less great for getting it back out. It's one of those "sounds good", but "doesn't work" ideas.

Thomas Franz Hall of Fame Points: 3915 More actions · Answer 9

Steve Jones - SSC Editor wrote:

I know a little "prompt" tool that could help here ;), that being said, I hate magic numbers more than nulls.

Querying for GETDATE() BETWEEN valid_from AND ISNULL(valid_to, '20991231') is not better - and you just moved the magic number into (every query).

And

SELECT * 
   FROM tbl as t 
 WHERE t.valid_from <= GETDATE() 
    AND (t.valid_to >= GETDATE() OR t.valid_to IS NULL)

is almost equal clumpsy / annoying as the original, where I had to compare to 18991231 instead of NULL.

So "magic numbers" are more than okay in my opionion for valid_to columns, even if we could have choosen the year 2999 instead of 2099 to give our descendants 900 more years. On the other hand, I doubt, that the current database will still be in place in 75 years, so I'm totally fine with 2099.

God is real, unless declared integer.

Steve Jones - SSC Editor SSC Guru Points: 737236 More actions · Answer 10

Agreed, though in the query I am picking a long future date, which could change and not necessarily impact the query. If I have to look for the value, then I find that more of a hassle to alter.

f.clems SSC Rookie Points: 29 More actions · Answer 11

Still searching how to be humorous at it...

3 keys (K1, k2, k3), each have 2 distinct generators for 2 different path P1, P2

P1: K3 ~ K1, K2 ~ K1 * K'

P2: K3 ~ K2, K1=K2

So K3 is sometime an entity independent on the context, sometimes an entity inside a context, what makes it a totally different concept/object to model

It's a type system without explicit type waiting for collision and my brain to explode.

f.clems SSC Rookie Points: 29 More actions · Answer 12