[selowitch]

If I'm developing a database with two tables, e.g., "Customers" and "Organizations." There are two ways I can name the primary keys of each (the primary key of the Organizations table being used as foreign key in the Customers table to take advantage of relational functionality):

Code:
Customers.id
Organizations.id

or

Code:
Customers.customer_id
Organizations.organization_id

Convention seems to favor the latter format, but isn't the former more compact and easier to predict? What is the advantage of the latter technique? It seems like most folks do it that way and I'm at a loss to explain why.

Ideas?

[trip]

Originally Posted by selowitch

If I'm developing a database with two tables, e.g., "Customers" and "Organizations." There are two ways I can name the primary keys of each (the primary key of the Organizations table being used as foreign key in the Customers table to take advantage of relational functionality):

Code:
Customers.id
Organizations.id

or

Code:
Customers.customer_id
Organizations.organization_id

Convention seems to favor the latter format, but isn't the former more compact and easier to predict? What is the advantage of the latter technique? It seems like most folks do it that way and I'm at a loss to explain why.

Ideas?

I believe it's an argument of code readability. Some would argue it's easier to read
Customers.customer_id because you know what the id represents when reading SQL.
If you did this everywhere you can imagine it would create more text in your SQL statement.

I would argue less words make cleaner code and for me, it's much easier to read.
I would use Id alone as an attribute.


edit: and as a note convention in my industry is Id. I've worked on 20 - 30 databases for data vendors who represent 100's of thousands of users and the only place I see customer_id is in my textbook.

[selowitch]

Originally Posted by trip

I believe it's an argument of code readability. Some would argue it's easier to read
Customers.customer_id because you know what the id represents when reading SQL.
If you did this everywhere you can imagine it would create more text in your SQL statement.

I would argue less words make cleaner code and for me, it's much easier to read.
I would use Id alone as an attribute.

edit: and as a note convention in my industry is Id. I've worked on 20 - 30 databases for data vendors who represent 100's of thousands of users and the only place I see customer_id is in my textbook.

I think I agree with you that "id" is sufficient. A close friend of mine with a lot of experience with very large databases argues passionately the other way, but I think I may go in the other direction this time.

[trip]

Originally Posted by selowitch

I think I agree with you that "id" is sufficient. A close friend of mine with a lot of experience with very large databases argues passionately the other way, but I think I may go in the other direction this time.

The other thing I don't like about customer_id is that it assumes a pluralization\singularization format. Not all tables can have that.

Example:
A customers table can be singularized into customer_id

If you had a table 'food' what would the id be ? Well you would logically pick food.food_id

However one might argue that naming convention is misleading. If I looked at food.food_id I could assume that the db convention is table.table_id, then if I wanted to write a sql statement for the customers table it should therefore be customers.customers_id, but it's not because the db designer chose to singularize when neccessary.

by using Id you resolve this.

[selowitch]

You do run into a problem when you do a join between tables each with a field called "id" rather than "food_id" or "painting_id" or whatever, in that it becomes difficult to properly reference the different fields.

[Millennium]

Originally Posted by selowitch

You do run into a problem when you do a join between tables each with a field called "id" rather than "food_id" or "painting_id" or whatever, in that it becomes difficult to properly reference the different fields.

How is "food_id" or "painting_id" necessarily more difficult than "food.id" and "painting.id"?

[selowitch]

Originally Posted by Millennium

How is "food_id" or "painting_id" necessarily more difficult than "food.id" and "painting.id"?

If you have done a join (say, in SQL), you don't always have the ability to reference a particular field in "table.field" format, so if you have two fields (in separate tables) each named "id" instead of something unique, it can create problems when, for example, you try to output your data.

[moodymonster]

I would go with having a unique id name, that way you could also store another table's entry eg

cust_id

sales_id, cust_id - where the sales table is storing the id from the cust table and using a WHERE to get the correct info from the cust table to display eg

SELECT sales_item, cust_name WHERE cust.cust_id=sales.cust_id

or join or however you want to do it.

I've used the technique here to hold the manufacturer's id in the handset table - so any changes to the manufacturer's info would apply across all the handsets:

http://www.mobilegamefaqs.com/compat...id=647&m=3

the query is "SELECT handset WHERE manfact_id=3"

[mondayisgreat]

I use short names, such as "id". It seems that every dba has his/her own philosophy.

[trip]

Originally Posted by mondayisgreat

I use short names, such as "id". It seems that every dba has his/her own philosophy.

True. I must say I am not a db so I may not be the best source. I am a software developer that writes software against databases.

From my perspective a consistent convention is important.

If I write code that goes into a database gets a list of tables then loops through and programatically defines adhoc sql queries, it saves on code and performance issues having a consistent convention. ie. computers can not determine when and how to singularize an attribute name.

I don't mind table.table_id, but I realllly don't like plural.single_id.

just my 2 cents.

[toffe]

let's say you have the table Person with filelds
name
title
customer_id -> Customer.id
organization_id -> Organization.id


and Customer with fields
id
name

---
That is more clear when you look at the table....

Sometimes when you select something from multiple tables, it can become confusing....
compare:
1 ) select * as customer from person,customer where person.customer_id=customer.id
2) select * from person,customer where person.customer.id=customer.id

the first one is better in my opinion, and the second one migth not even work.

[mondayisgreat]

You can do this: [can't figure out how to indent properly with the tags of vbulletin]

select
p.id person_id, c.id customer_id
from
person p,
customer c
where
p.customer_id = c.id

[moodymonster]

http://dev.mysql.com/tech-resources/...alization.html

this will give you info on how to design databases

[zwiebel_]

I always name my table first followed by field, i.e. Customers.customer_id. Very easy to identify the field once you start joining tables.

[blackpenguin]

Originally Posted by zwiebel_

I always name my table first followed by field, i.e. Customers.customer_id. Very easy to identify the field once you start joining tables.

I agree, generally all of my tables use a primary key of the form <table>.<table>_id. This reduces confusion that can potentially occur with the simple "id" field in 20-30 tables and allows for simplified joins. Just think: if you have a table of orders and a table of items, and a lookup table for the two, the join is much easier if you structure like this:

orders
orders.order_id
....

items
items.item_id

order_items
order_items.order_id
order_items.item_id

No confusion, no redundant fields, and the join language is simple.