<< Yet one more reason to like ReSharper | Home | BooBS Sample >>

Schema to wince by...

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...

Print | posted on Wednesday, October 03, 2007 12:12 AM

Feedback

Gravatar

# re: Schema to wince by... 10/3/2007 1:18 AM Avish

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.

Gravatar

# re: Schema to wince by... 10/3/2007 1:37 AM Michael

He is probably referring to the hungarian naming convention. And if he's not, forget I wrote this post :)

Gravatar

# re: Schema to wince by... 10/3/2007 2:25 AM Ayende Rahien

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;--

Gravatar

# re: Schema to wince by... 10/3/2007 2:49 AM Andrew Hallock

I never understood why people repeat the table entity name in the column name, as in "EmployeeName" instead of just "Name."

Gravatar

# re: Schema to wince by... 10/3/2007 5:12 AM Liang

Declare Field Name with data type. Now the bar is very low to become a programmer. :=)

Gravatar

# re: Schema to wince by... 10/3/2007 6:14 AM Tom Opgenorth

And here I was thinking Oren was concerned about the misspelling of "Serial". :)

Gravatar

# re: Schema to wince by... 10/3/2007 6:52 AM sradack

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.

Gravatar

# re: Schema to wince by... 10/3/2007 8:24 AM Christiaan Baes

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.

> I never understood why people repeat the table entity name in the column name, as in "EmployeeName" instead of just "Name."

They probably also have a proper name. employeename is what the boss calls them and name is what their mother used to call them.

Gravatar

# re: Schema to wince by... 10/3/2007 9:39 AM Anamoly

I agree with iEmployee..rial..
But otherwise, the naming conventions (like 'vc' prefix) seems to be okay ..

Gravatar

# re: Schema to wince by... 10/3/2007 9:56 AM Michael Hawksworth

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 ;) )

Gravatar

# re: Schema to wince by... 10/3/2007 11:01 AM Ken Egozi

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:
1. no one noticed (bad)
2. people were too afraid to make a change (really bad)

Gravatar

# re: Schema to wince by... 10/3/2007 11:05 AM Avish

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?

Gravatar

# re: Schema to wince by... 10/3/2007 5:53 PM Jon Skeet

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).

Gravatar

# re: Schema to wince by... 10/3/2007 6:14 PM Ayende Rahien

Jon,
I use the alias name for that.

emp.Id = salary.Employee

Gravatar

# re: Schema to wince by... 10/3/2007 6:54 PM Ayende Rahien

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.

Comments have been closed on this topic.