Prev: Semicolons
Next: Unexpected Join behavior
From: Erland Sommarskog on 17 Jul 2010 09:18
Tom Cooper (tomcooper(a)comcast.net) writes:
> Like Erland said, there are losts of different conventions that people use
> today. I personally prefer camel case and don't like underscores because
> they're awkward to type,

Don't know how that looks on an English keyboard, but on a Swedish keyboard,
the underscore was driving me nuts, because it requires use of the shift
key. Pain_when_everything_else_is_lowercase.

Eventually, I had enough and found a tool that permits me to define on
my own keyboard layout, and put underscore on the place where � is on a
Swedish keyboard - a character I have far less use for. Typing is so
much easier now!

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: --CELKO-- on 19 Jul 2010 11:12
I spent a couple of years at AIRMICS looking at the research on code
readability for the US Army. Your eyes are taught to jerk to an
uppercase letter -- in Latin, Greek, and Cyrillic alphabets, they
signal the start of a paragraph, sentence, proper noun, or other
"start of something special". CamelCase is demonstrably bad.

But a few centuries of using lined paper make underscores much easier
to read. Your eye flows across them. I summarized all this stuff in my
SQL PROGRAMMING STYLE.
From: Eric Isaacs on 19 Jul 2010 17:57
On Jul 15, 3:38 pm, "Tom Cooper" <tomcoo...(a)comcast.net> wrote:
> 4) in many shops the primary key of (virtually)
> every table is named ID, don't do that.  It's employeeid, EmployeeID,
> employee_id, etc, but not ID.

Expanding on what Tom Cooper suggested regarding EmployeeID rather
than ID, I would even go further and suggest that you use the table
name at the beginning of the fields in that table that are not foreign
keys.

Compare these two designs...

CREATE TABLE tblEmployee
(
ID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
SupervisorID INT
)

....where SupervisorID relates back to tblEmployee.ID for the
supervisor's record.

CREATE TABLE tblEmployee
(
EmployeeID INT,
EmployeeFirstName VARCHAR(50),
EmployeeLastName VARCHAR(50),
Supervisor_EmployeeID INT --You could also use
EmployeeID_Supervisor
)

....you can tell that Supervisor_EmployeeID relates back to
tblEmployee.

Also, when you join this table with another table that has FirstName
and LastName fields, you don't have to alias the result columns to
make them unique. It's automatically standardized. When you alias it
back to itself to determine the supervisor name and employee name
together, it's pretty clear how you would alias those columns in the
results to keep consistency (just prefix the supervisor's fields with
Supervisor_.)

Foreign Keys should maintain the name of the primary key, unless they
need to be further clarified in order to distinguish them from other
foreign keys from the same table.

-Eric Isaacs


From: --CELKO-- on 21 Jul 2010 01:22
>> I would even go further and suggest that you use the table name at the beginning of the fields [sic: columns are not ANYTHING like fields!] in that table that are not foreign keys.<,

your head is the the right place but your experience is not. We "seven
times always" name a thing for what it is (pardon the Kipling
reference). Structure is "seven times never" put into the relationship
table.

CREATE TABLE Personnel -- collective table name
(ssn CHAR(9) NOT NULL PRIMARY KEY
CHECK (ssn LIKE ['0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

CREATE TABLE OrgChart
(.. );
\
From: Eric Isaacs on 21 Jul 2010 03:10
Joe,

We've had these disagreements before. There's nothing new here
today. But my experience says that your advice is nothing more than
oversimplifications that work well in the textbook world but don't
always pan out in the real world.

My simple example was just to illustrate a simple concept. Your
simple example also shows a similar lack of real world experience.
Using an SSN as a primary key for the personnel table just doesn't
work in the real world. Not every employee has a SSN, and you may not
know the SSN when the person is entered into the personnel table, but
a primary key is required for the record to be created. If you
require a SSN, the users will work around it by using false SSNs and
therefore creating data anomalies which are much worse than using a
surrogate key as the primary key.

By using field names that include table names, you identify what it is
and where it came from which self-documents the database making
development and maintenance easier. If you have a view that includes
EmployeeFirstName, there's no doubt that the field originated from the
Employee table. You don't have to research it or review a data
dictionary to see where it came from. You also don't have to alias
an EmployeeCreateDate field whereas you may need to alias a CreateDate
field from the Personnel table if you use that in a view with another
CreateDate field from a related table.

Jim,

The important thing to remember is that consistency is much more
important than what you actually decide to do. Having standards is
more important than what exact standards you select. Developers will
get used to them. At first, some developers will disagree with
certain standards, but in the end everyone will get used to them.
There are pros and cons to each and every standard or design choice
you make. You have to decide which is better for you and your
situation and then reap the rewards and deal with the consequences.

-Eric Isaacs
First  |  Prev  | 
Pages: 1 2
Prev: Semicolons
Next: Unexpected Join behavior