1) Is 3rd normal form a good goal to shoot for when designing a DB? Or, is that probably "too normalized" in practice because it's probably "asking for trouble" later on when trying to optimize?
I would personnaly say the goal is all normal forms that can be reached. I have studied and studied these to make sure I fully understand them (even thou they are a bit heady in the way they are
written), but the Normal forms are about addressing flaws than compactness of data (just tends to lead to that). The key is if you can you insert the data without a required other condition, can
you delete a row without remove a key piece of info that would then be lost, and can you update the record without a syncing issue. Normalize as full as possible then denormalize as needed for
performance issues, but look for alternatives first if at all possible.
2) Is there a rule of thumb about having "too many tables in a query?" Does it indicate a problem if a query that is supposed to do some basic functionality of an application joins like 8-10
tables? Or is this normal?
No rule other than performance concerns. I have joined as many as 17 and there was no noticeable or concernable delay in the query. Sometimes thou join slow downs can be address with temporary
tables to do some of the gathering instead of denormalizing.
3) When should the decision be made to de-normalize the DB? Should it be before you write the DB or, in small to medium projects, is it okay (and probably expected!) to do a re-design mid-way?
Obviously the earlier you can get the design right the better but I'm asking more about people's experience in practice
See answer to 1 for parital. As for redesign it is very common to make changes thruout the design to improve upon. Just make sure you set a list of standards and keep good documentation. Might also
consider look at some of the books on Refactoring and X-treme Programming. Also look here,
http://www.extremeprogramming.org/
http://www.instantiations.com/jfactor/files/refback.pdf
But keep in mind altought helpfull I am not too sure that the extreme points reached are totally needed such as refactoring an existing stable application for the sake of code reuse. My opinion is
that if it isn't broke, don't touch it unless you really have nothing better to do.
4) What are people's thoughts about triggers/constraints for a DB? Many developers (and that's my background) seem to want to do all the constraining in their application. Personally, I don't
like that approach. Well, to clarify, I don't think it's enough. I think you should put in application constraints but the best place to put them in is the DB. I put in some constraints on my DB
but it's hard (and complicated) to put in (as well as think of) constraints for every possible situation. I guess some constraints is better than none though, right?
Every thing you do in regards to database should be to reach the goal of complete data intergrity. Constraints, triggers, business logic in Stored Procedures are tools you can use to reach that
goal as a data safety net. However putting too much logic in the DB can also be a bigger perofrmance issue than a gain. Weight your pros and cons and test when unsure. Generally thou good
constriants and triggers are going to save you lots of potential headaches long term.
5) When in the development lifecycle should constraints be put into place? In my project, I spec'd out my application first. Then I did my DB design. Then I wrote my constraints. Then I wrote
my code. Does that sound like the correct approach or is it "inadvisable" to write the constraints/triggers too early because a project is likely to change a good amount as it is developed.
As soon as you recognize a paritcular object should be finitely controlled you should have a defined constraint for it. You can do immediately and always adjust these as needed. Odds are you
actually would get better implementaion by following a circular design approach. This however can be a personal preference situation and may also have to consider the overall complexity of the final potential product, the more complex the more you may want to build both the front and back at the same time for slow methodical testing to ensure the flow and design work hand in hand. Or, you may decide the backend is fairly straightfoward and build it first. It is similar thou to the question of where to start eating a sandwhich made with loaf bread, some prefer a speicifc corner, some will start in the middle of a praticular side, and some will cut it in hald or a wedge and start in the heart of the sandwhich. So in essence there is no right or wrong answer there.