Table/Column Naming Conventions (Opinions Wanted)
-
I'm curious about a direction i'm heading in building some tables and want some opinions on whether this is a stupid idea.
Instead of naming the primary key columns like EmployeeId, or CompanyId, or OrderId, etc., i'm naming them just plain [Id]. I started down this path because it looks kinda repetitive to say the name of the table is Employee and the primary key is EmployeeId; point is that the column ends up being Employee.EmployeeId and the way i was going would make it Employee.[Id].
Here's an example of what I'm doin:
Create Table Company
(
[Id] int identity not null primary key
)
Create Table Employee
(
[Id] int identity not null primary key
, CompanyId int not null references Company([Id])
)So the question is, is this a lame strategy?
Another question is on naming tables. I got in the habit of naming my tables with singular version like "Employee" instead of "Employees". I like that way but I'm wondering if I'm the only one who likes that way. Anyone have an opinion on the tables being plural or singular names?
thanks,
chris
-
As long as it makes sense to you and you are sure the next person coming along will understand convention is always up to you. Personally I prefer to use
tbl_Employees
simply because I am sure it is a table and what it contains. I may also thou have a view titled
vw_Employees
which joins to items referenced in the table by Identity values instead of the words. But with Procs because of hits you take with using sp_ (see "What is the search order for Procedures prefixed sp_?" and related conversation http://www.sqlservercentral.com/columnists/jtravis/sp_performance.asp)
I choose Procs to use ip_ or isp_ where others choose up_ or usp_ to avoid the issue.
-
I always use singles, not plurals, for table names: tbl_employee, tbl_employee_history.
I would not recomment naming column as just Id. First of all, it is a reserved word (it changes color in QA - see, you have to use []). Secondary, you should sort of inforce domains through yuor naming convension. If you name Employee ID and Company ID both as Id, those appear as if they related to the same domain. I prefer company_email and employee_email instead of email and email.
-
Thanks for the replies. Decided to lose the [Id] concept and use the more meaningful names like EmployeeId. I like the tbl_ prefix for tables but that didn't go over too well with the others, maybe next project. We are gonna try to keep table names singular just for consistency. Thanks again for the responses.
-chris
-
quote:
Thanks for the replies. Decided to lose the [Id] concept and use the more meaningful names like EmployeeId.That is unfortunate. mromm makes a good point regarding enforcing domain integrity with naming conventions, however, if the SQL was written properly, with table names prefixing field names, then a purely object-oriented naming convention would lead to easy-to-understand schema designs and SQL. For example, if you have a table, Employee, and a table, Company, and PK fields both called ID, and FK field in Employee table referencing Company table would be called Company, then proper SQL would read:
SELECT Employee.ID , Company.Name FROM Employee INNER JOIN Company ON Employee.Company = Company.IDIMHO, I think this is an elegant, object-oriented version of a naming standard. Personally, I've never understood why DBAs and DB designers never cottoned on to the object-oriented naming standards and continue to use outdated, antiquated COBOL and mainframe naming standards with a bunch of underscores and repetitive prefixes and suffixes.
Hope this sheds some light on another viewpoint...
Cheers.
-
I understand the points about OOP made in the previous message. In the same time, SQL language itself does not support OOP.
A column named ID will be used without qualifying it with a table name by your developers. There is no enforcement mechanism like you have in OO languages. Therefore, you cannot rely on it or even think in the OO terms. Besides, ID is a reserved word.
After all, it might be just a metter of taste.
-
quote:
There is no enforcement mechanism like you have in OO languages.There are no mechanisms in any language to enforce simplicity.
quote:
Besides, ID is a reserved word.And system tables and procs routinely use this keyword.
quote:
After all, it might be just a metter of taste.true about that...
[/quote]
-
Maybe a bit late, but I'd like to toss in two cents. (Arguing about naming conventions is safer and more fun than arguing religion or politics.)
First off, I inherited the "use singuler table names" practice; I suspect someone somewhere tossed a coin.
For primary key identity columns (surrogate keys), I use "<tableName>_ID". The key advantage to this is in the use of foreign keys. Imagine having a table that references several other tables by primary key, and imgaine if all those keys in the source tables were named "ID". If the name of the table being referenced is part of the column name, then it's a lot easier to figure out (make that "decipher", if someone else built it) the structure of the database, and a lot easier to read the code using the tables.
Having different names for every occurance of the same data item just makes it harder to remember what to call a given instance when you're writing code, and makes it tougher to comprehend what you're reading. Remember, some day someone totally unfamiliar with the work you are doing *right now* is going to be working with your code, and you want to make it easy on them. Particularly as that person may be you!
This also supports domain integrity for individual columns, in that every time it appears it is identified by the same name. Domain integrity for (the class of) all such surrogate keys could perhaps be enforced by (a superclass of) User Defined Data Types--something I don't know too much about.
As for object oriented programming techniques, as said they are good and appropriate for object oriented languages, but not so useful in other development environments. Use methods that support the tools you are using, not the tools you'd rather be using!
Philip
-
Good points you make, but I think you missed my point.
If you make the field name "ID", then referencing TableName.ID makes perfect sense, even in joins, where it would be ParentTable.ID = ChildTable.NameOfEntity...
example:
SELECT * FROM Order
INNER JOIN OrderDetail
ON Order.ID = OrderDetail.Order
I can't think of a simpler, more intuitive coding style. Naming the key field TableName_ID is more verbose, and redundant if I include the table name in the query (which is always in the FROM clause anyway...)
Second, UDTs don't enforce anything. They are almost a waste of time, since you can't modify a UDT once it is created...
-
Have to agree with the more verbose and redundant naming convention using TableName_ID (or something similar).
I believe the point mromm made about enforcement not being present is a good one in this discussion. I have run into problems too often by people not qualifying the full object, and thus running into problems sooner or later.
-
Firstly, use a naming convention that maximizes the readability of your code. Readability was a key learning point in the "Worst Practices - comments" series.
Secondly, using the tablename.fieldname notation is good practice as it is clear and unambiguous.
It has been discussed elsewhere but I use
SELECT ObviousAlias.Id
FROM dbo.tablename AS ObviousAlias.
Note the use of dbo.tablename
I do tend to use prefixes a la Hungarian notation. Apparently this is old hat, but as I deal with developers of various vintages it works for me.
Some DBAs complain that prefixing objects means that navigation within EM is slower i.e. if every table begins with tbl you can't jump to the first table beginning with 'A'. Tough.
Lastly, whatever you choose, stick to it, document it so any new guys have a point of reference.
-
quote:
I always use singles, not plurals, for table names: tbl_employee, tbl_employee_history.The advice I received was to use either the plural of the object or to use the singular form of the name of the collection of objects. In this example, the table would either be named Employees or Personnel.
I've always disliked the use of the singular object. You can end up with syntax such as "Employee.Employee".
$.02
Steve Hendricks
MCSD, MCDBA
AFS Consulting Group
(949) 588-9800 x15
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15 -
quote:
I've always disliked the use of the singular object. You can end up with syntax such as "Employee.Employee".One should never name a column Employee. It should be EmployeeID, or EmployeeName, etc.
The problem with mixing singles and plurals is that your convention is harder to follow. IT will be violated at some point inavitably. It is much simpler to enforce the "always single" rule.
-
Some good comments. Here are mine. (your_value = Free_Advice * my_2_cents)
Table Name - singular or plural: Singular is commonly used, because the (real-world) entity is singular; the attribute "Name" is for a single Employee. For O-O work, this works well when you have separate classes for collections (plural name) and individuals (singular).
Using "Hungarian prefixes" (e.g., tbl..., int...) - These belong in programs, not in the database. For the tbl prefix on tables, I want people to concentrate on the data (Employee) not the implementation. I should be able to substitute a view for the table if neccessary down the road. In a program using ADO, you probably want to use "cmdEmployee" for an ADO.Command object for accessing the Employee table, and "rstEmployee" for an ADO.Recordset with the results; the tbl prefix just gets in the way.
Column names prefixed with a datatype prefix are bad. Consider an Integer that has to become a Big/Long Integer -- do you want to go and keep the old (no longer "correct") name, or make someone change all the programs that have already been written? Also, there are the non-arithmetic digit-only fields -- should they be CHAR or INT? Why put that in the name?
Columns Names begin with Table Name -- Typically this is better because we are NOT doing O-O. (1) We can produce a view which includes a join. Now we have to change from Customer.Id to CustomerId to show that "Id" came from Customer not Order. Especially true with recursive relationships/joins: Boss.EmployeeName, Peon.EmployeeName. (2) I should be able to switch from a table to a view without a lot of trouble, so I should not have to make the above change in programs. (3) Tables have foreign keys for maintaining relationships, while Objects normally hide this and provide methods that return the related object via a pointer or object-id.
(4) Helps prevent errors when Primary Key and Foreign Key has the same EmployeeId name. You don't get accidental joins of Customer.Id = Order.Id because you forgot the prefix, and some end-user query tool jumped to a bad conclusion based on matching names.
Column Names with Data Class Words -- A "data class word" is something like "Date," "Name," "Id," "Qty," "Pct," etc. that describes the category of data in that column. Its often enough to name the field, or make it readable. You should have a small, standard list of these appropriate to your industry and require all column names to end with one. Helps spot problems: ~~Qty * ~~Amt = ~~Amt makes sense, but ~~Qty + ~~Amt = ~~Amt does not.
Association (Join) Tables -- Name these for the relationship expressed by the table, not the tables that are being related. (A) You may have multiple relationships between two tables. If not now, in the future. (B) Users and newbies may think the table represents one kind of relationship, when it actually represents something else. The result is confusion, and possibly corrupted data. Again, singular names: each row describes a single instance of the relationship.
Arcane naming minutia -- A lot of the "old-timers" carry over habits because of limitations that used to exist. Older DBMSs and programming languages didn't allow anything but uppercase letters, digits and underscores; to make things readable, you had to stick the underscores in the names. Nowadays, we should probably avoid using spaces in names, but use capitalization to separate words. When writing naming standards, remember to specify how to handle acronyms and abbreviations, and decide how to handle adjacent aronyms (one time when an underscore is really a good idea).
On your naming conventions, remember to consider "role names" for foreign keys when you need to avoid duplicating column names (See the "Boss/Peon" example above). How do you handle it? BossEmployeeId, EmployeeBossId, EmployeeIdBoss, or BossId? Each has pros and cons; I usually prefer the first because "Boss" is the feature (O-O term) and "EmployeeId" is the domain/data-class-word. This comes up rarely enough to be handled inconsistently without a written standard, but often enough to for the inconsistency to become apparent over time.
David Lathrop
DBA
WA Dept of Health -
As an expert and consultant for Databases and SQL, I always give this standard :
1) SQL NAMES
------------
Must be on SQL standard wich mean :
1.1 limited to characters [A .. Z] + [0 .. 9] + [ _ ]
1.2 length under 128 characters
1.3 begining by a letter
1.4 having not many underscore following
1.5 case don not matter, but SQL Server is case sensitive so, all names must be in capital letters
1.6 do not be a SQL reserved word, nore a SQL Server reserved word.
2) OBJECTS BY TYPE :
--------------------
every basic objetc must have a prefix :
2.1) Tables
for tables the prefix is
T_ for a table "entity"
TJ_ for a table joining
TR_ for a table wich is a reference for one or many tables (by example, titles of persons like M. Mrs....)
TG_ for a generic table (example person table wich will be completed by employee table, customer table, ...)
TS_ for table wich are nor business data, but technical data for the use of the database (examples user tables with login and password)
2.2 other objects:
VIEW => V_
DOMAIN => D_
RULE => R_
USER TYPE => U_
3 TABLE SUFFIX
--------------
Every table must have a suffix of 3 letters
with is the contraction of the table name.
A prefix must be strictly unique in the database.
4 COLUMN NAME
-------------
Every column name must began by the suffix of the table except in the case of foreign key wich must have strictly the same name of the key referenced.
The name of the column must not be ambigous and must indicate in wich data topic the column is typed (alpha, num, temporal, binary...)
A calculated colum must be suffixed by CALC
5 EXAMPLE :
***********
CREATE TABLE TR_TITLE_TTL
(TTL_ID INT NOT NULL PRIMARY KEY,
TTL_TITLE CHAR(8))
CREATE TABLE TG_PERSON_PRS
(PRS_ID INT IDENTITY NOT NULL PRIMARY KEY,
PRS_NAME CHAR(32),
TTL_ID INT,
PRS_SOUNDEX_CALC CHAR(4),
PRS_BIRTHDATE DATE)
CREATE TABLE T_CUSTOMER_CST
(PRS_ID PRIMARY KEY NOT NULL,
CST_COMPANY_NAME VARCHAR(64))
CREATE VIEW V_CUSTOMER_CST
AS
SELECT CST.PRS_ID AS CST_ID,
PRS_NAME AS CST_NAME,
TTL_TITLE AS CST_TITLE,
PRS_BIRTHDATE AS CST_BIRTHDATE,
CST_COMPANY_NAME
FROM T_CUSTOMER_CST CST
INNER JOIN TG_PERSON_PRS PRS
ON CST.PRS_ID = PRS.PRS_ID
LEFT OUTER JOIN TR_TITLE_TTL TTL
ON PRS.TTL_ID = TTL.TTL_ID
...
PLEASE, do not forget that SQL Server is CASE SENSITIVE with SQL names !
PS : I have right a complete paper on the topic, but this paper is in french.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply