homepage Welcome to WebmasterWorld Guest from 107.20.30.170
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Good field naming practice?
Mr_Cat




msg:4406989
 2:27 pm on Jan 15, 2012 (gmt 0)

Hi folks,

Sorry if this is too open ended or general or something, but I was wondering...

I have a database with several tables, most of which contain fields with ID numbers, chunks of text/info, author names, and dates.

Is it good database practice to make these unique, or does it depend on the context, or does it not matter really?

To be more specific I've looked into/learned the JOIN statement using php sql recently to retrieve my data, had some problems and tried to make things more unique incase there was a conflict somewhere, i.e. my fields look something like 'table1_ID, table1_text' etc instead of simply 'ID' and 'text' as they previously did.

In the tutorial page I just found that finally enlightened me to the correct use of JOIN in a fashion I understood, the author was saying "because we have two fields with the same name (in the example two table db for the tutorial) they make ideal candidates for JOIN"...this led me to think 'does it matter?'

Could it lead to any kind of conflict in more complex queries, or is it sometmes better to have field names the same on different related tables.

Any thoughts would be great, thanks :)

 

mark_roach




msg:4407261
 12:20 pm on Jan 16, 2012 (gmt 0)

It is quite normal to have columns with the same name in different tables and this should not cause you any problems. If you need to reference a column which appears in more than one table in your query you just prefix it with the table name.

eg.

Select tablea.*, tableb.* from tablea, tableb where tablea.ID=tableb.ID

tangor




msg:4407414
 8:12 pm on Jan 16, 2012 (gmt 0)

The tables are unique (separate) and column headers could be the same in the tables. If that information is the same, the relational aspect is more obvious for search/join functions.

rocknbil




msg:4407427
 8:27 pm on Jan 16, 2012 (gmt 0)

"because we have two fields with the same name they make ideal candidates for JOIN"


Maybe it's because it's out of context, but two fields having the same name is not really a good case for using them as a join field. :-) A better case is that both fields are numeric fields with the "parent" table being a unique value. Numeric seeks will always be faster (joining "1234=1234" rather than "rocknbil=rocknbil".)

Sometimes you can name joining field names indicative of what they join to. For example an message board with users, posts, forums, could have a join table for the user's posts like so:

id userid forumid postid .....

Obviously, the userid will join the user's table on the user record id (or userid) and forumid would join the forum descriptions table on that table's id.

A decent naming convention in anything (table fields, variable names, anything) is such that the field name is somewhat indicative of the field content. Consider

id | a| b| c| d | e

versus
id | user_id | fname | lname | address | city | state

This part is fine as long as you know what's happening

Select tablea.*, tableb.* from tablea, tableb where tablea.ID=tableb.ID


Say table a is users, table b is something related to users, like posts in a message board.

id | userid | fname | lname | email

id | userid | postid | postitle | forumid | postcontent | posted_date

(see below for why I chose "posted_date")

turn to your programming: in PHP (and other languages) there are methods to store a row in an array, so the above example would give you an associative array containing these keys:

id, userid, fname, lname, email, id, userid, postid, posttitle, forumid, postcontent, posted_date

As you can see there's two keys for id and userid, so requesting

$row['userid']

.. only pulls the first one. In this example the result is identical, they are the same values, but you can see the potential for confusion or incorrect results. As long as you're aware of what happens selecting all with *, it's not a big deal. You can also select specific fields, which is more efficient than * anyway:

Select tablea.username, tableb.posttitle from tablea, tableb where tablea.userid=tableb.userid and tableb.postid=12345

Where field naming can get sticky: field names like this

id first name | last name | datetime

are actually allowed, but you have to back tick them (for two different reasons) to work properly. The names have spaces, and datetime is a mysql reserved word. You should avoid reserved words, but if you must use them, you must make a habit of backticking:

select `first name`, `lastname`, `datetime` from `table` where `id`=1234

So to answer the question, it does matter, but only if you expect someone to understand your code easily in the future.

mark_roach




msg:4407433
 8:43 pm on Jan 16, 2012 (gmt 0)

Just to add to rocknbil's post

Select tablea.username, tableb.posttitle from tablea, tableb where tablea.useridid=tableb.userid and tableb.postid=12345


If you also wanted to retrieve the ID value from each table you can use "as" to make the column names unique.

eg.

Select tablea.ID as tablea_ID, tableb.ID as tableb_ID, tablea.username, tableb.posttitle from tablea, tableb where tablea.useridid=tableb.userid and tableb.postid=12345

And then retrieve the values using

$row['tablea_ID']
$row['tableb_ID']

Mr_Cat




msg:4407956
 12:08 am on Jan 18, 2012 (gmt 0)

Hey thanks for all the thoughts people, this is great stuff, I do love this site :D

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved