Time to have a rant about third-party vendor databases. Yes, I'm looking at you Sage, VersionOne, and most especially, YOU, Hybris CRM.
Yes, you. Mr. 'Let's not use clustered indexes.' 'Let's model our schemata using ORM.' 'Let's call our primary key column 'PK' on almost every table. Let's not bother naming any of our constraints. And using stored procedures or functions - that's so 1990s, man.
I mean, why NOT use BIGINT for every single integer column. Or VARCHAR(255). No, wait a minute, that's way too efficient. Let's use NVARCHAR(255) instead. For everything.
Take a look at this, part of the table definition from a vanilla Hybris CRM installation:
CREATE TABLE [dbo].[addresses](
[hjmpTS] [bigint] NULL,
[TypePkString] [bigint] NOT NULL,
[PK] [bigint] NOT NULL,
[createdTS] [datetime] NOT NULL,
[modifiedTS] [datetime] NULL,
[OwnerPkString] [bigint] NULL,
[aCLTS] [int] NULL,
[propTS] [int] NULL,
[p_dateofbirth] [datetime] NULL,
[p_middlename2] [nvarchar](255) NULL,
[p_streetname] [nvarchar](255) NULL,
[p_contactaddress] [tinyint] NULL,
[titlepk] [bigint] NULL,
[p_phone1] [nvarchar](255) NULL,
[p_remarks] [nvarchar](255) NULL,
[p_firstname] [nvarchar](255) NULL,
[p_phone2] [nvarchar](255) NULL,
[originalpk] [bigint] NULL,
[p_fax] [nvarchar](255) NULL,
[p_shippingaddress] [tinyint] NULL,
[p_streetnumber] [nvarchar](255) NULL,
[p_gender] [bigint] NULL,
[p_url] [nvarchar](255) NULL,
[p_district] [nvarchar](255) NULL,
[countrypk] [bigint] NULL,
[p_lastname] [nvarchar](255) NULL,
-- continues on for a loooooong time...
'p_gender' - BIGINT??? Really? So there's 2^64 -1, or 18,446,744,073,709,551,615 possible genders, are there? On what planet? Or how about 'PK' for the primary key? In column 3, of all places? I sincerely hope the user isn't intending to put some long ASP.NET-generated URL in 'p_url', since it's limited to 255 characters. And 'p_dateofbirth' clearly needs to store time information too, to the millisecond, as that's important when profiling your customers.
I'm dreading the day when we actually start using your hated system in production and your table scans and parameter-sniffed execution plans slam into my servers like a colossal tidal wave of shit. I can absolutely guarantee that when I ring the vendor to complain I'll get through to some poor sod on a mere handful of Vietnamese Dong per hour telling me 'prease to call back in mornink'. Believe me when I say I'm beginning to batten down the hatches now, and when 'main screen turn on' I'll be hiding in the server room, watching our monitoring software with my fist in my mouth.
Sage, you're not off the hook. No matter how hard you insist, a database is not a 'company'. It's a database. I admire your overall design, placing your metadata in a separate DB and company / entity-specific information in separate DBs. I don't appreciate you writing metadata to 'master' and bursting into tears when I remove it. Nor, frankly, are your table structures much cop either. It's not hilarious to dress up your SQL Server-related errors in another error wrap, spreading confusion and delay among the various support teams:
'An unexpected error has occurred. SQL code 208.' (Sage)
=
'Msg 208, Level 16, State 1, Line 1 - Invalid object name ...' (SQL Server)
Why not just return the second message? The problem will land in the lap of the DBAs anyway, won't it? Save time! 'An unexpected error has occurred' simply means 'Something went wrong and I (the developer) can't be fucked to write a coherent error message, preferring to let support teams scramble to look up SQL Server error codes in sys.messages until the application dies a death and they switch to something written properly.'
Vendors! Wake up! It's time to start reconsidering old-fashioned concepts like 'testing' and 'good design'. I know we're all about Agile now, but 30 years ago we had good, solid texts like 'The Theory of Relational Databases' (David Maier - free eText here) full of things like ... wait for it ... relations! Functional dependencies! Normalisation! I know, yawn, yawn, why not just let nHibernate create it all for us, yeah, yeah ... But there's good, solid systems out there right now using databases built on these basic principles.
Vendors, don't give up your day job. Develop great apps and leave the database administration to the professionals.
The information and views expressed in this article are my own and do not represent the opinion or views of my employer or any third-party.
No comments:
Post a Comment