Dealing with collections without the Connect software?

[kovidgoyal]

Ah since I know next to nothing about databases, I'm going to ask what advantage you get from storing the authors independently of the books?

[HarryT]

OK - "Database 101" time .

The goal of any database design is to only store each item of information once, and once only. This is called "normalisation".

In my database, I have a table of authors ("tAuthor"), which has an author's name, and a ID, which is simply a number. Eg:

1: Asimov, Isaac
2: Dickens, Charles
3: Austen, Jane
...

I then have a list of books ("tBook"), again each one with a ID:

1: Bleak House
2: Pride and Prejudice
3: Sense and Sensibility
4: Oliver Twist

Finally, I have a "link table" ("tLnkAuthorBook") which contains pairs of numbers - an author ID and a book ID:

AuthorID BookID
2 1
2 4
3 2
....

This means that author number 2 (Dickens) is an author of book number 1 (Bleak House); author number 2 (Dickens again) is an author of book number 4 (Oliver Twist), etc.

The benefit of this is that any book can have as many different authors as you want (multiple rows in the link table with the same Book ID) and any author can have as many books as you want (multiple rows with the same author ID.

So, to find all the books that Dickens wrote, I just have to search the link table and find all the rows where the author ID = 2. Having got those rows, I look up the details of the book from the book ID. I can do this with a SQL statement such as:

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

This will give me a list of all the titles of the books for which Dickens is an author (or a co-author).

Sorry, that's very brief, but hopefully you get the idea! In reality, I don't have to type in the SQL by hand - I just have a form where I can choose an author from a drop-down list, and I'll get a list of the details of all the books that the author wrote.

[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.

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.

[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.

[kovidgoyal]

Thanks, you've opened up lots of vistas for me to explore. I do need to learn more SQL.

[HarryT]

My pleasure!

[Azayzel]

Tough thing with dealing with SQL queries is they vary slightly from DB to DB. We had quite a few different research projects going on at my last job where I'd have to deal with mySQL, PostgreSQL, MS SQL, and Oracle. Talk about a pain in the butt getting all the query syntax mixed up when I hadn't used a particular system in a while!

In the end, I think I liked mySQL a bit better than the rest; while they all have their pro's and con's, mySQL had the best value... being free (so is PostgreSQL, but mySQL was more widely used and had a nice freeware GUI for Windows you could use mySQLPal)!

[kovidgoyal]

Yeah but I doubt mysql is suitable for an enduser app. I'm not going to ask my users to run a mysql server. There are libraries that present a unified API for all the databases, for e.g. sqlalchemy

[Thiana]

Originally Posted by kovidgoyal

Yeah but I doubt mysql is suitable for an enduser app. I'm not going to ask my users to run a mysql server. There are libraries that present a unified API for all the databases, for e.g. sqlalchemy

Just use SQLite... No seperate install required.

[kovidgoyal]

yeah that is what i use at the moment.