Log in

No account? Create an account
entries friends calendar profile Previous Previous Next Next
Working on the SQL - shadows of echoes of memories of songs — LiveJournal
Working on the SQL
Read 13 | Write
From: vatine Date: August 18th, 2008 06:19 pm (UTC) (Link)
Let'sd prefix with "I am not a database expert, but..."

Try to avoid duplication of data, until duplication of data is speed-critical.

Try to store each conceptual "thing" in a table row of its own, using many-to-many tie-tables when there's assorted interlinking between related "things" (typical example where I did the DB design myself and am not entirely displeased with the result; one table for books that hold "id", "title", "isbn", "classification" [this is for my own sheer enjoyment], "publisher" [an ID reference to a publisher table]; one for authors that hold "non-sur name(s)", "surname" and assorted bio data when known and a book-author tie table that holds tuples of book-id and author-id).

Having loked at the image, it' snot too far off what I've mostly seen. In general, database sketches tend to describe teh tables and their direct inter-linking.
barnacle From: barnacle Date: August 18th, 2008 07:43 pm (UTC) (Link)
Normalization - which is the de-duplication of data - definitely helps optimize your databases performance, but at the cost of complicating your application. It has to hold more table joins and conditions in its declarative layer, to reassemble the data into something meaningful. The level of normalization should support the application, and you can always (with some pain) normalize more in future.

The Art of SQL is a fascinating, surprisingly hard-headed book. But it's not a great beginning book, and there's only one or two large bits that I've had the patience to sit down and read straight through. The O'Reilly books for the SQL variants are generally OK.

Your db diagram looks fine, j4. You might want to flip the arrowheads, so they look like cartoon snowmen arms and hands. Then the single line means "one from this table" and the multiple line means "can be joined to many from this". Putting a line across the single end (so making it a cross) means it's specifically one-to-many (a line being a stylized 1) and a circle means zero-to-many. But that's just disambiguation.

Outside of just getting data in and out, hardcore optimization comes from knowing about indexes (and sequences) and, if adding indexes on foreign keys doesn't work its magic, being able to figure out the query plan output from the SQL client of your choice, and maybe just changing the order of your table joins to give the planner the hint it needs.

If you're using PostgreSQL, learn about these things; if you're using MySQL, just keep plugging away until performance starts to suffer in twelve months' time, then learn about them; if you're using Oracle or SQL Server, then set fire to the server and head to G&D's while the sirens come and go.
From: (Anonymous) Date: August 19th, 2008 09:10 am (UTC) (Link)
I'll second The Art of SQL as an excellent book to read eventually, though not beginner-friendly.
Read 13 | Write