Quote from: samba at Aug 23, 2006, 02:35 PM
Good points, thanks for the ideas. This was the direction I was leaning in. It’s clearly true that having queries joining 5 tables gets a little gnarly. And joining 5 tables sure seems as though it could be slow.
...
Depending on how the database is used, I can see situations where normalization may actually help performance. Say you have a huge number of records. If you have a query that only goes after one 3-field table, as opposed to going after a giant 25-field table, disk reads should be faster, no?
Absolutely. Scalability and performance is a balancing act, and database normalization definitely weighs in.
In general, joining by foreign keys to normalized dependent tables does not necessarily increase or decrease performance, it completely depends on what kind of and quantity of data is being stored and retrieved. And though normalization does make transaction handling a little more complex, it generally increases insert/update performance, and improves your ability to get at specific parts of the data more quickly, and only when you need it. This can reduce memory footprint and increase query performance in certain situations where granular views of the individual, normalized tables would be useful.
Another consideration in normalization is maintainability and extensibility. Ask yourself if this is something you’re going to use as-is forever, or if there other features you may want to add in the future that could benefit from normalization? One thing you don’t want to deal with is migrating data to a new, normalized (or de-normalized for that matter) model because you have to change it later on, especially if you have a lot of data. It’s possible the migration is worth the extra milliseconds of performance you gained until the new features were introduced, but at least in more complex data models, this can be a real pain in the rear.
Just keep in mind that designing a web application requires you to consider the specific requirements of your application against the extremes of under-normalization and over-normalization of your data model. I for one tend toward normalization in most cases, but I’ve worked as a database administrator for several major corporations, managing, migrating, importing, and exporting every imaginable kind of sales and marketing data, so my experience helps guide me through the decisions. It’s a daunting task, designing a database, but can be one of the most important aspects of your web application, so I encourage you to continue to research the subject, and even experiment with different structures to see how they actually fare.
Quote from: samba at Aug 23, 2006, 02:35 PM
(Basically, in terms of db-design, redundancy is not something that "could" occur - it is there or it isn’t.)
Maybe I’m a little off-track, but here’s a thought. Let’s say my one monster table contains a field called State. My thought was that I may have only one record containing "Alaska", even though it would be possible to have many. Wouldn’t that be a situation where redundancy may or may not occur? Sorry to split hairs. 
This is just another one of those decisions where it depends on how often the data is going to change. IMO, unless you are going to be internationalizing your site, data that doesn’t change very often, like states, is better maintained in code (i.e. a PHP array representing a normalized table) or even just in the HTML markup (i.e. form select options) and saved by name. And you can easily migrate such a structure to a normalized one later by simply adding a new fk column to the new table, and setting the values based on the unique set of values you store in your new table. Remove the old column, and bam!, instant normalization. But the performance benefits of storing state un-normalized, since it’s something that will only be changed (or managed) rarely, are obvious, if not trivial.
MODX Staff
