RE: FN-FORUM: Advanced Databases for Beginners

date posted 6th September 2006 16:48

Quoting Mark Bell [EMAIL REMOVED]

> Dave,
>
> Why is this so?
>
> Bad example:
>
> CREATE table book (
> book_id INTEGER,
> book_title VARCHAR(100),
> book_author_id INTEGER
> }
>
> Good example
>
> CREATE table book (
> id INTEGER,
> title VARCHAR(100),
> author INTEGER
> )
>
> I agree with the title and author tables, (book_title/ book_author, bad ba=
d
> bad) but normally I use a specific id field - ie: 'book_id' or maybe 'bid=
'
> so if a select statement requires id's from multiple tables that are easie=
r
> to distinguish between.
>
> What's on the downside of naming an id field in this way?

Well, to be honest, not much. A lot of advice on naming conventions =20
comes down to choosing a set of rules that you like and sticking with =20
it. And if you prefer a primary key to be called {table}_id then I =20
don't really have any strong opinions against that.

Personally, I like the fact that every table will have a column with =20
the same name that is the primary key of that table. To me, that just =20
makes the database easier to use. And I think that when you are =20
joining multiple tables then you should be using explicit table names =20
(or aliases) for all the columns so the confusion would never arise. =20
For example:

SELECT book.title
FROM book
JOIN author
ON book.author =3D author.id
WHERE author.name =3D 'Someone'

In your case, the only clause that would change would be the ON clause =20
which would become:

ON book.author =3D author_id

which really isn't different enough to worry about in my opinion.

A lot of the slides aren't really trying to say "this is the only way =20
to do it" but rather "think about why you're doing this and ensure you =20
have a good reason". Of course that might not come over quite so =20
strongly when I'm not actually there giving the talk.

Dave...

--=20
Magnum Solutions Ltd
Open Source Consultancy, Development and Training
http://mag-sol.com/