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.
![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
no subject
Date: 2008-08-18 07:43 pm (UTC)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.