Including a - plural - table prefix to the table name related to the project, separating tables from different applications in a same database. BLOG_users
Plus, I put this prefix uppercase separated by an underscore (for better & quicker visibility). Same as you for field naming, I keep a prefix made with the table name, uppercase too. USR_name

Jonathan Snook said on August 03, 2005

JohnO: not all database servers support that multi-insert format (I kept the article database generic) -- MySQL may be the only one (if anyone is aware of Oracle or Postgresql or SQLite support, I'd be glad to hear it)

Adam Ham said on August 03, 2005

To the best of my knowledge, Oracle 10g does not support multiple inserts with the syntax JohnO has used above.

Here is the Syntax for an Oracle 10g Insert Statement.

There is way to insert multiple values with one select statement in Oracle 10g; although it seems more like a hack. Use an insert select statement as shown below.

INSERT INTO users.table (field1, field2)
(SELECT 'test value', '1' FROM dual
UNION SELECT 'test value', '2' FROM dual
UNION SELECT 'test value', '3' FROM dual);

Jani Tarvainen said on August 05, 2005

About the naming conventions... Aliases help keep complex queries more readable, regardless of the table names.

"SELECT author AS postauthor FROM posts"

DD said on August 05, 2005

Great article John! I've even posted a link to my blog to this article because it is a great introduction to databases.

Might there be a part 2 to look forward to?

Wesley Walser said on August 05, 2005

I have a feeling that your putting up this article is in some way related to your final question on this article.

Jonathan Snook said on August 05, 2005

Wesley, you astute reader, you! Yes, it partially came out of the observation that introductory topics seem to generate more traffic. But more often than not my topics come from talking to friends or co-workers or poring over comments on previous posts. Issues that they run into when jumping into something become inspiration for stuff to write about (hey, it's a difficult task to come up with interesting stuff to write about all the time). And as DD has requested, a part two just might be in order. :)

Moises Kirsch said on August 08, 2005

On the naming conventions.... I use something similar:

1. All table names are plural
2. I create a 2 o 3 letter prefix for each table based on the name wich then is added to the column names using an underscore.
3. When using a foreign key you also add the name of the original table (that way you know where that key is coming from).
4. Camel Base names (to make it easy to read).

So if I have a table called 'Users' the columns names would be usr_id, usr_fullName, usr_email.

Then the Posts table coul have values like pst_id, pst_title, pst_body, pst_usrId_writer

This way you know exactly what each values means and where it comes from.

Iota said on August 08, 2005

You can also do joins solely in the WHERE clause, which I find more fluid. Using the blog example above, you might have a user table:


And a posts table:


To join the two we could do the following:

SELECT u.username, p.subject, p.content
FROM users u, posts p
WHERE = p.userid;

Which would leave us with a resultset showing username, subject and content for each post in the blog.
You may also notice how you can create aliases for table names to save typing the full table name over and over again in the select section of the query.

Geoff said on August 08, 2005

Jonathan, you write:
"A blog has articles and comments. Both an article and a comment would each be an object. AUTHORS and COMMENTS would be our tables."

Don't you mean ARTICLES and COMMENTS would be our tables?

Sorry if you find posts correcting your articles annoying, feel free to delete this.

Kevin Hale said on August 09, 2005

This is going to be a great article for a lot of people. Thanks for sharing.

Jonathan Snook said on August 09, 2005

Iota: I'll actually be going into some depth as to why I use the INNER JOIN syntax in a followup post. :)

Geoff: I've fixed the mistake. I love posts pointing out any errors I've made. Every little bit helps!

matthijs said on August 20, 2005

Indeed, interesting article Jonathan. I'm looking forward to the next one too ;)

Jude said on December 25, 2005

Re: Iota

The join using a list of tables (and no JOIN) is an implicit CROSS JOIN.

Snook calls these "Crazy Joins" in the next article. Could become very inefficient, I guess.

Sorry, comments are closed for this post. If you have any further questions or comments, feel free to send them to me directly.