|Good field naming practice?|
| 2:27 pm on Jan 15, 2012 (gmt 0)|
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 :)
| 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.
Select tablea.*, tableb.* from tablea, tableb where tablea.ID=tableb.ID
| 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.
| 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
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
.. 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.
| 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.
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
| 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