+972 52-548-6969
Schema to wince by...
time to read 1 min | 176 words
I was just sent this table schema (minor modifications to protect the responsible party):
CREATE TABLE [dbo].[tblEmployees] ( [iEmployeeSirial] [int] IDENTITY (1, 1) NOT NULL , [vcEmployeeID] [nvarchar] (15) COLLATE Hebrew_CI_AI NULL , [vcEmployeeName] [nvarchar] (50) COLLATE Hebrew_CI_AI NOT NULL , [vcAddress] [nvarchar] (60) COLLATE Hebrew_CI_AI NULL , [vcCity] [nvarchar] (30) COLLATE Hebrew_CI_AI NULL , // many more fields )
I am not going to enjoy the integration phase...
Comments
Can you, uhm, give us, like, a hint as to what exactly is wrong with it? It's not that we don't know, right? It's just that we, er, want to make sure we see the same, uh, immediately apparent faults as you do.
He is probably referring to the hungarian naming convention. And if he's not, forget I wrote this post :)
The naming convention is a real PITA.
"iEmployeeSirial" foretell quite a bit about the rest of it, as well.
In my experience, usually such schemas are often accompanied by a great deal of SP and quite a bit of business logic in the DB.
I tend to make snap decisions on the quality of a code-base according to its characteristics. This is wholly unscientific method to doing so, but I find it to be fairly accurate as far as I am concerned.
The last such schema I have encountered was proudly presented to me as a completed secured SP-based system, which cracked the first time I tried to search for 1'; Drop Database Prod;--
I never understood why people repeat the table entity name in the column name, as in "EmployeeName" instead of just "Name."
Declare Field Name with data type. Now the bar is very low to become a programmer. :=)
And here I was thinking Oren was concerned about the misspelling of "Serial". :)
I was curious what the problem was as well. I don't like the Hungarian notation but I didn't really see that as a huge problem. Thanks for the clarification.
isn't hungarian supposed to be 3 characters?
And nvarchar should be nvc, how lse are you going to make the difference between varchar and nvarchar.
They probably also have a proper name. employeename is what the boss calls them and name is what their mother used to call them.
I agree with iEmployee..rial..
But otherwise, the naming conventions (like 'vc' prefix) seems to be okay ..
Ex Foxpro developer maybe.
Lots of them do this as that is what the standard said to do at one point and once its been hammered in it is hard to drop a style.
It does have its benefits in separating fields from variables (something linq users will have to get used to). I still write variables liSomeVariable and parameters tiSomeParameter then have change them. Force of habbit.
In my book, this comes into the same category as prefixing tables with tbl, making the id field in a table TableNameID (EmployeeID) or just ID or x or y. House rules, and if the house says do it xyz you do it xyz. The good bit is when you set the rules and can have the ones you like( which someone else will no doubt think are wrong ;) )
This looks suspiciously familiar.
I have seen similar stuff in my days.
things like:
tblScale (a table for storing Skill types ...)
CARES table (for storing details about cars. Misspelled, upppercase, no PK, no Unique on license plate, ...)
Your'e gonna have some really good times Ayende
Anyway, as I see it, the real problem is not that they use some weired notation. It could be a matter of taste. however, spelling mistakes are definitely mistakes, and the fact that they stayed in a live system means one of the two:
no one noticed (bad)
people were too afraid to make a change (really bad)
I agree with the "DB shouldn't have business rules in it" notion, but that whole "SPs are bad" attitude only works if you've got NHibernate (or any other OR/M tool) instead... right?
Given a "naked CLR" as you once called it, where would you put the business logic that affects database queries? I don't want to embed it as SQL strings, I don't have anything to generate that SQL for me, and I sure as hell don't want to just grab all the data and filter/join in C# code. What do I do then?
This is not a hypothetical question; that's what we have at work. We use typed datasets as the data model and avoid manual OR mapping. What do we do about queries? What can we do to step up from this position?
With regards to prefixing the column name with the table name ("EmployeeName") - I agree in general, with the exception of identity columns. It's nice to see joins on foo.EmployeeID = bar.EmployeeID to make it blatantly obvious that it's appropriate.
On the other hand, that only works when it's clear which employee you're talking about. For instance, it would be reasonable to have
foo.ManagerID = bar.EmployeeID to make it clear that it's foo's manager (and not some other randomly related employee).
Jon,
I use the alias name for that.
emp.Id = salary.Employee
Avish,
The whole SP is bad is a reaction to trying to put stuff in the DB and finding how painful that can be later on.
Having a tool help _tremendously_, but building a QueryBuilder is very easy to do, giving you the ability to do:
From(Tables.Users)
.Select(Tables.Users.Id, Tables.Users.Name)
.Where(Tables.Users.Date > DateTime.Today)
.Execute<NameAndId>();
A bit of code gen can give you quite a bit of power, and require hardly any tooling whatsoever.
I don't like the naked CLR, it is a useful building block, but you don't even have the raw foundations. That is why I use so many libraries, to make it easy to start from a solid base.
Comment preview