Should any column names be avoided? Using prefixes?

Dev Shed Forums Sponsor:

Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

December 16th, 2002, 08:04 AM

RyanA

Junior Member

Join Date: Jul 2002

Posts: 23

Time spent in forums: < 1 sec
Reputation Power: 0

Should any column names be avoided? Using prefixes?

Example:

TABLE names
id
name

TABLE cars
id
type

Do the column names matter? I recall reading something (I think it was on form variables) that said certain things should be avoided and I've noticed that many databases use prefixes in the column names.

Example:

TABLE customer_names
c_id
c_name

TABLE customer_cars
c_id
c_type

Does it make any difference and, if so, under what conditions?

Last edited by RyanA : December 16th, 2002 at 08:07 AM.

December 16th, 2002, 10:25 PM

ngahleng

Registered User

Join Date: Mar 2002

Location: singapore

Posts: 16

Time spent in forums: < 1 sec
Reputation Power: 0

for persistent is
tableA
id
name
-------------
tableB
id
name
---------------
in query always make
select a.id,b.id from tableA as a, tableB as b;

December 17th, 2002, 03:18 AM

NoXcuz

Wiking

Join Date: Sep 2000

Location: Sweden

Posts: 3,608

Time spent in forums: 7 h 49 m 27 sec
Reputation Power: 11

Just find a scheme that fit your needs and stick to it. There are many different approaches, but problems might arise when you want to use table or column names like WHERE, DATE, TEXT and various function names, as they are reserved words according to ANSI SQL and also differs between databases.
That's why many people use prefixes for their columns names. Like

names
--------
nam_id
nam_name

or

names
--------
naId
naName

That way they won't interfer with reserved words or functions, and it can ease identifying which table they belong to by looking at the prefix. Also PK's and FK's can be easier to identify if you use some kind of naming convention, and queries can be easier to interprit that way.

Just a few thoughts...

//NoXcuz

__________________
UN*X is sexy!
who | grep -i blonde | date; cd ~; unzip; touch; strip; finger; mount; gasp; yes; uptime; umount; sleep

December 17th, 2002, 07:47 AM

ngahleng

Registered User

Join Date: Mar 2002

Location: singapore

Posts: 16

Time spent in forums: < 1 sec
Reputation Power: 0

my last posted is too simple.
some of the field is common to most table.

cars
-------
id
year
engine_nr
name
id_color // or color_id
id_owner // or owner_id

colors
------
id
name

owners
--------
id
name
emailto

for query readable
SELECT car.id,car.name,color.name,owner.name FROM cars as car,colors as color owners as owner WHERE 1 AND car.id_color=color.id AND car.id_owner=owner.id;

i somtime cannot recall what i defined field name|id ... in table. but i know there hv id,name.....

December 17th, 2002, 07:56 AM

RyanA

Junior Member

Join Date: Jul 2002

Posts: 23

Time spent in forums: < 1 sec
Reputation Power: 0

Then there are reserved words. I will continue to prefix my tables. It's funny, because when I was naming the columns there was a field renamed to date that really stuck out. I knew there was a reason I didn't like the way it looked, now I know why.

What are PK's & FK's?

December 17th, 2002, 08:35 AM

NoXcuz

Wiking

Join Date: Sep 2000

Location: Sweden

Posts: 3,608

Time spent in forums: 7 h 49 m 27 sec
Reputation Power: 11

PK = primary key
FK = foreign key
A small example might explain it...

Three tables: books, authors and book_author. Books contain a primary key that uniquely identifies each row (bo_id), by using a auto incremented integer. Authors also contain a primary key to uniquely identify each author (au_id). The table book_author is a 'lookup' table that contains the foreign key bo_id which references the primary key in the table books, and it also contains the foreign key au_id which references the primary key in the authors table.
By combining these two PK's in a separate table, you know which author who wrote which book. It also touches on the subject of normalization, but that's in another chapter...

Code:

books
+-------+-------------------------+
| bo_id | bo_name                 |
+-------+-------------------------+
|     1 | Harry Potter            |
|     2 | Lord of the Rings       |
|     3 | the Old Man and the Sea |
+-------+-------------------------+

authors
+-------+------------------+
| au_id | au_name          |
+-------+------------------+
|     3 | Ernest Hemingway |
|     1 | J.K. Rowling     |
|     2 | J.R.R. Tolkien   |
+-------+------------------+

book_author
+-------+-------+
| bo_id | au_id |
+-------+-------+
|     1 |     1 |
|     2 |     2 |
|     3 |     3 |
+-------+-------+

SELECT 
  b.bo_id, 
  b.bo_name, 
  a.au_id, 
  a.au_name 
FROM 
  books b INNER JOIN book_author USING (bo_id) 
  INNER JOIN authors a USING (au_id)
+-------+-------------------------+-------+------------------+
| bo_id | bo_name                 | au_id | au_name          |
+-------+-------------------------+-------+------------------+
|     3 | the Old Man and the Sea |     3 | Ernest Hemingway |
|     1 | Harry Potter            |     1 | J.K. Rowling     |
|     2 | Lord of the Rings       |     2 | J.R.R. Tolkien   |
+-------+-------------------------+-------+------------------+

//NoXcuz

December 17th, 2002, 08:43 AM

RyanA

Junior Member

Join Date: Jul 2002

Posts: 23

Time spent in forums: < 1 sec
Reputation Power: 0

I see. It's a good thing I didn't commit any code changes to work with the non-prefixed column names. Thanks for the info.

December 17th, 2002, 11:10 AM

MattR

Contributing User

Join Date: Jun 2001

Location: High above the mucky-muck (Columbus, OH)

Posts: 266

Time spent in forums: < 1 sec
Reputation Power: 8

Personally, it base tables I never prefix.

Why? Because it is obvious in the book table that you are talking about a book name.

And if you are joining to another table with a name column, you simply type:

Code:

SELECT co.name,
        b.author
  FROM book        b,
       checked_out co
 WHERE b.name = co.name

So the prefix is there 'when you need it' rather than *always* being there. For example if you are not joining the bo_name is redundant and you *always* must use it. The table.name convention should be more than enough, and you only need to use it when necessary.

Of course, in relationship tables or with foreign keys I do usually prefix, typically:
checked_out( user_name_FK, book_name_FK )
or
checked_out( user_name, book_name )
or even
checked_out( user, book )

I haven’t really decided on whether or not to adopt the _FK convention… Currently for foreign keys I do: (generic)
some_table( table_foreign_key_column_name, table2_foreign_key_column_name )
(example)
owns( user_name, book_title )

generally the foreign key table prefix makes sense since you may not know the table it comes from, and it retains the column name so you don’t have to look it up.

__________________
Matt - matt@fanhome.com
FanHome.com - Where Sports Fans Connect (our SYBASE-backed vB Forums!)
Sybase DBA / PHP fanatic

Sybase v. MySQL v. Oracle | Why I don't like MySQL

| Download Sybase TODAY! | Visit DBForums.com for all your RDBMS talk!

Last edited by MattR : December 17th, 2002 at 11:30 AM.

December 17th, 2002, 11:28 AM

MattR

Contributing User

Join Date: Jun 2001

Location: High above the mucky-muck (Columbus, OH)

Posts: 266

Time spent in forums: < 1 sec
Reputation Power: 8

Generally reserved words do not come into play if you use descriptive naming. In a book table what does a column of name ‘date’ mean? Date you bought it? Date it was printed? Purchase_date (or purchaseDate or whatever your naming convention is) and printing_date makes it clear what the data holds and avoids reserved word collisions.

I can see, though, if you want the date a book was checked out.
checked_out( user_name, book_name, date )

oops! Reserved word!

So in this case I’d use something like checked_out_on or the like, because – it could be the date they returned the book, or the date it is due, etc. Can’t take everything for granted.

#10

December 17th, 2002, 12:18 PM

RyanA

Junior Member

Join Date: Jul 2002

Posts: 23

Time spent in forums: < 1 sec
Reputation Power: 0

Well, I'm building a program that stores WarCraft III replays. Originally I had every column name prefixed with it's table name. In the wc3_replays table I begain with submitted_on, but since I didn't need to store another date I simplified it to date. (prefixed it was replay_date). Here's what the database looks like:

PHP Code:


		
			
 wc3_players

    id

    name

    rank



wc3_maps

    id

    name



wc3_realms

    id

    name

    url



wc3_gametypes

    id

    name



wc3_versions

    id

    name

    

wc3_replay

    id

    gametype_id

    map_id

    player_id1

    player_id2

    player_id3

    player_id4

    player_id5

    player_id6

    player_id7

    player_id8

    realm_id

    version_id

    description

    hits

    length

    name

    rating

    replay

    submitted_by

    submitted_on

    votes

Is there anything you would do differently?

#11

December 17th, 2002, 12:32 PM

MattR

Contributing User

Join Date: Jun 2001

Location: High above the mucky-muck (Columbus, OH)

Posts: 266

Time spent in forums: < 1 sec
Reputation Power: 8

The wc3_replay table does not look normalized at all!!

Just as an aside if the tables are living in their own database -- say wc3, the wc3_ prefix is a little redundant. If they're living in a bigger replay database then perhapse we can normalize things into a single set of tables.

I also tend to keep my tables in the singular tense; player vs. players, map vs. maps, but that's a personal thing.

Since we have a player and replay table I wold rip out the player_id1-8 and do:

wc3_replay_player( replay_id, player_id )

I don't know what votes is, but that could be broken up into a votes table so you can keep track of who voted.

#12

December 17th, 2002, 01:17 PM

RyanA

Junior Member

Join Date: Jul 2002

Posts: 23

Time spent in forums: < 1 sec
Reputation Power: 0

Quote:

Since we have a player and replay table I wold rip out the player_id1-8 and do:

wc3_replay_player( replay_id, player_id )

That thought never even crossed my mind. Just when I thought I was getting good at it this..

Quote:

If they're living in a bigger replay database then perhapse we can normalize things into a single set of tables.

Yes, the tables will be a part of a large database that contains more than 100 tables. What do you mean by normalizing things into a single set of tables?

Last edited by RyanA : December 17th, 2002 at 01:19 PM.

#13

December 17th, 2002, 01:31 PM

MattR

Contributing User

Join Date: Jun 2001

Location: High above the mucky-muck (Columbus, OH)

Posts: 266

Time spent in forums: < 1 sec
Reputation Power: 8

Well if you are storing many different game replays (say Warcraft2,3; Age of Empires, Age of Mythology) you could have a game table:
game( ID, name, author )

Then in your replay table you'd have a gameID (or in one of the other tables that made sense). So you could add new games on the fly and not have to create new tables to store essentially redundant data.

But if you are doing only WC3 then don't worry about it.

#14

December 18th, 2002, 06:31 AM

RyanA

Junior Member

Join Date: Jul 2002

Posts: 23

Time spent in forums: < 1 sec
Reputation Power: 0

Ah, nope. It's just for WC3. I have one last question with this database.

Primary Keys are automatically INDEXed, so with a table such as

PHP Code:


		
			
 wc3_replay_player

    player_id

    replay_id

..should I just set player_id as the primary key or should I not set a primary key and simply index it? My instincts say to PK it, but I've never used a PK for anything other than an autoinc field.

I suppose I should index both fields. Does the table need a PK?

Last edited by RyanA : December 18th, 2002 at 06:38 AM.

#15

December 18th, 2002, 01:01 PM

niico.c

Contributing User

Join Date: Sep 2002

Location: France

Posts: 55

Time spent in forums: < 1 sec
Reputation Power: 6

Quote:

Originally posted by RyanA

Primary Keys are automatically INDEXed, so with a table such as

PHP Code:

wc3_replay_player player_id replay_id

..should I just set player_id as the primary key or should I not set a primary key and simply index it? My instincts say to PK it, but I've never used a PK for anything other than an autoinc field.

I suppose I should index both fields. Does the table need a PK?

I think a table always need a PK!

And a PK can be made of 2 fields.

Here you should put (example for postgresql)

CREATE TABLE w3_replay_player (
player_id INT REFERENCES w3_players, --FK
replay_id INT REFERENCES w3_replay, --FK

PRIMARY KEY (player_id, replay_id) --PK with two fields
);

It's a quite simple syntax, it may be different according to your DB.

You can also use :
CREATE TABLE w3_replay_player (
player_id INT REFERENCES w3_players, --FK
replay_id INT REFERENCES w3_replay, --FK

CONSTRAINT w3_replay_player_pk PRIMARY KEY (player_id, replay_id),
CONSTRAINT w3_replay_player_player_id_fk REFERENCES w3_players(player_id),
CONSTRAINT w3_replay_player_replay_id_fk REFERENCES w3_replay(replay_id)
);