Database Field Naming Conventions

[jbubrisk]

Hi guys,
A little database design question:

In general does it make sense to name your "id" fields just "id", or something like "cust_id" and "emp_id" (for employee). It seems that if you just used "id", things would not only be confusing, but a pain in the butt!
If you joined the two tables, and did a "SELECT * FROM", you would always need to alias the fields (employees.id as emp_id) so that you could efficiently access the data.
However, if you renamed the fields to cust_id and emp_id, and joined the tables, no such renaming would be necessary.

Does that make sense? Are there any detrimental side affects that I am missing?

Thanks in advance,
jbubrisk

 

[Markbnj]

It's a good question, and it's similar to the whole namespace vs. qualified names debate. The question is how often people will see that field without the table name, and have to interpret what it means? The issue doesn't arise in simple contexts like a database management tool. You'll almost always have the table name when browsing DB objects. So it's really a matter of what the code will look like. If the code is sufficiently descriptive then custTable.ID is a fine identifier. But tbl1.ID is less so. At least you should be able to assume that ID is the primary key of whatever table is in tbl1. I guess I have no problem either way, as long as it's consistent. The only thing long identifier names saves is some typing, so I lean toward more information rather than less.

 

[Crusty]

No matter what you choose, be very consistent with it.

 

[troytime]

i've always preferred cust_id or emp_id or news_id, etc etc

there's been a few times where i left it at id, but its always been a temp table or an unimportant table

 

[Argo]

I prefer cust_id because then things like that are a lot easier to understand:

Table CUSTOMERS (cust_id, name, email)
Table ORDERS (order_id, cust_id, ...)

Now replace primary keys with "id" in the above scenario and see if it's as clear.

 

[Crusty]

Table CUSTOMERS (id, name, email)
Table ORDERS id, customer_id, ...)

Is my preferred method 🙂

 

[jbubrisk]

Thanks for the comments guys. Speedy too! I think I'm going to rename the tables to the "full_id" method. I think its much more readable, and it make more sense for the joins.

 

[ruijorge]

This is what I use:

Customers (Id,Name,Email)
Orders (Id, Customer,...)

 

[Crusty]

Originally posted by: ruijorge
This is what I use:

Customers (Id,Name,Email)
Orders (Id, Customer,...)

Capital letters take too long to type 😀

 

[reverend boltron]

I definitely go with the "customer_id". I used to be all about the "id", but then I got tired of it after ending up with errors due to ambiguity (because of my laziness).

 

[JACKDRUID]

I would either use only "ID", or I would write out the full "CustomerID", never abbreviate.

 

[Descartes]

Originally posted by: JACKDRUID
I would either use only "ID", or I would write out the full "CustomerID", never abbreviate.

+1

Abbreviations are absolutely atrocious. I follow strict rules with abbreviations; it's only safe to abbreviate when the abbreviation is an industry standard. HTML, HTTP, XML, etc. are all acceptable as industry terms, but CustAssgnDt and nonsense like that are unacceptable. I see it all too often though.

 

[troytime]

Originally posted by: Descartes

Originally posted by: JACKDRUID
I would either use only "ID", or I would write out the full "CustomerID", never abbreviate.

+1

Abbreviations are absolutely atrocious. I follow strict rules with abbreviations; it's only safe to abbreviate when the abbreviation is an industry standard. HTML, HTTP, XML, etc. are all acceptable as industry terms, but CustAssgnDt and nonsense like that are unacceptable. I see it all too often though.

i abbreviate some stuff..but only if its still obvious.

my pet peave is when column names contain the data type (even in abbreviated form)
iuserid
vchnickname
vchemailaddress
textdescription

or when people put _tbl after all their tablenames...OMFG is that retarded
users_tbl
passwords_tbl

 

[Snapster]

Originally posted by: troytime

i abbreviate some stuff..but only if its still obvious.

my pet peave is when column names contain the data type (even in abbreviated form)
iuserid
vchnickname
vchemailaddress
textdescription

or when people put _tbl after all their tablenames...OMFG is that retarded
users_tbl
passwords_tbl

Oh god that just reminded me of a database I recieved once! I hope never to see that again! 🙂

 

[Crusty]

Originally posted by: Snapster

Originally posted by: troytime

i abbreviate some stuff..but only if its still obvious.

my pet peave is when column names contain the data type (even in abbreviated form)
iuserid
vchnickname
vchemailaddress
textdescription

or when people put _tbl after all their tablenames...OMFG is that retarded
users_tbl
passwords_tbl

Oh god that just reminded me of a database I recieved once! I hope never to see that again! 🙂

When I first started at my current job, everything was a huge mess just like that. No two databases had any sort of common naming conventions or common authentication system and on top of that all the application code was a huge mess too... luckily I changed that rather fast 😛

 

[Markbnj]

You have to love refactoring in VS and Eclipse. Very handy sometimes.

 

[KB]

I have my own set of database naming rules (everybody does)

Table CUSTOMER (CustomerID, Name, Email)
Table ORDER (OrderID, CustomerFK, ...)

Table names shouldn't be plural.
No underscores in any names, only Stored Procedure names can have underscores.
Foreign Keys actually have FK in the name.

 

[troytime]

Originally posted by: KB
I have my own set of database naming rules (everybody does)

Table CUSTOMER (CustomerID, Name, Email)
Table ORDER (OrderID, CustomerFK, ...)

Table names shouldn't be plural.
No underscores in any names, only Stored Procedure names can have underscores.
Foreign Keys actually have FK in the name.

i like the FK idea

i use underscores in table names...which i guess is kinda odd because i don't use underscores at ALL in code (camelCapsForMe)

all my association tables have _asn at the end
it started out as a joke, but it stuck and now the rest of the programmers here do it too

 

[clamum]

Originally posted by: Crusty
Table CUSTOMERS (id, name, email)
Table ORDERS id, customer_id, ...)

Is my preferred method 🙂

I used to do "cust_id" for the customers table and "order_id" for the orders table, but these days I'm doing it just like in your post.

 

[JACKDRUID]

Originally posted by: KB
I have my own set of database naming rules (everybody does)

Table CUSTOMER (CustomerID, Name, Email)
Table ORDER (OrderID, CustomerFK, ...)

Table names shouldn't be plural.
No underscores in any names, only Stored Procedure names can have underscores.
Foreign Keys actually have FK in the name.

other than the FK convention, I do everything the same.

I do find intCustomerID helpful, just that I'm too lazy now.

 

[thirtythree]

I haven't worked with databases a lot, but I just use id, then I'll use tablename_id for foreign keys. But, if I was doing lots of joins like you say, I'm sure it would make sense to use tablename_id for primary keys too.