[HarryT]

Originally Posted by kovidgoyal

Hmm I see the advantage of having things only one in the database. A not so obvious one is that you can do sorting at insert time by having a separate table for each sortable field.

That's not really true; data in a table in a relational database is not generally thought of as being "sorted"; you can sort the results of any query by adding an "ORDER BY" clause. Eg:

SELECT * from tBook ORDER BY SERIES, SERIES_POSITION

will display a list of books sorted by series name, and by series position within each series.

OK one more question:
Suppose I wanted to do a match for a search term over several fields. What would be the most efficient way to do that in this kind of setup? Is it just a bunch of inner joins? How efficient (fast) is that?

Thanks.

"Joins" specify RELATIONSHIPS between tables, rather than search conditions (although they can be used to do certain types of search). Eg, in my previous example:

SELECT Title FROM tBook INNER JOIN tLnkAuthorBook
ON tBook.ID = tLnkAuthorBook.BookID
WHERE tLnkAuthorBook.AuthorID = 2

The:

tBook INNER JOIN tLnkAuthorBook
ON tBook.ID = tLnkAuthorBook.BookID

join condition means that the "ID" column in the "tBook" table is the same value as the "BookID" column in the "tLnkAuthorBook" table.

The normal way to specify search conditions is in the "WHERE" clause of the query. If you're simply testing for equality, or for a range of values, provided that the table is "indexed" on the value you're doing the search on, it's amazingly fast (indexes are stored as "b-Tree" data structures, which are extremely rapid to search). We use databases at work which have millions of rows in them, and you can do SQL searches which return values from them in a tiny fraction of a second. It only gets slow if you do something like a "wildcard" search where an index can't be used, and the database has to "scan" each row in the table in turn to see if it matches the search condition.

SQL is well worth learning - it's a "universal" interface to databases and you can do amazing things with it. There are lots of good SQL books in any computer bookstore.