Database Theory Questions/Discussion
Posted: Fri Oct 28, 2005 10:21 am
Hello all,
I've been working with databases for a number of years now.. but they have mostly been pretty small and the need to have them setup "perfectly" hasn't really been there. (FYI i use mostly mySQL with a few msSQL DBs) Lately I have decided to convert all of my myIsam tables over to INNOdb and begin using foreign key relationships and transactions. I'm still working on this conversion and the implementation of the foreign keys has me altering many of my tables to have the fields the same size and indexed as well as making sure that the unsigned value of the integers is the same.
All this has me thinking.. (which can be dangerous at times
) which of these field settings are important to get correct in order to have the most robust db possible. Here are a few questions I'd like to have discussion on.. (I'm kindof assuming that they warrant discussion as opposed to just a simple answer.. but simple answers are good too
) And FWIW any of the practices i'm revealing in my questions are also up to discussion/flaming .. I suppose what i'm getting at is a DB Best Practices thread.. soo.. here we go
1. All of my Primary and foreign keys are currently integers. Since the foreign keys are relating to all primary keys (in this case) and those primary keys are, somewhere along the way, created by an auto-incrementing integer.. should they all be unsigned or is this very important at all?
2. When I have a field that is selected to be "Not NULL", when a row in its table is inserted generally the default value (usually 0) is inserted into this field. this, to me, totally defeats the purpose of "Not NULL". I would rather have it error out to my application so I can fix the code.
3. What does "Zerofill" mean exactly? Is it the same as making the default value of the field 0?
4. Foreign Key constraint "Cascade on delete" or "cascade on update". I don't quite get how the cascade applies to my database. (it might not) My tables are all fairly seperated by data entity so I don't understand how updating a child row should affect the parent row. (any examples?)
5. char vs. varchar: from what i understand, char takes up more disk space (and memory maybe?) while varchar takes up less space but uses more processing power.
I'm getting to the point where my databases need to be more enterprise worthy and I think this might be a good start for me (and hopefully many others as well)
Bring it on!
Will
I've been working with databases for a number of years now.. but they have mostly been pretty small and the need to have them setup "perfectly" hasn't really been there. (FYI i use mostly mySQL with a few msSQL DBs) Lately I have decided to convert all of my myIsam tables over to INNOdb and begin using foreign key relationships and transactions. I'm still working on this conversion and the implementation of the foreign keys has me altering many of my tables to have the fields the same size and indexed as well as making sure that the unsigned value of the integers is the same.
All this has me thinking.. (which can be dangerous at times
1. All of my Primary and foreign keys are currently integers. Since the foreign keys are relating to all primary keys (in this case) and those primary keys are, somewhere along the way, created by an auto-incrementing integer.. should they all be unsigned or is this very important at all?
2. When I have a field that is selected to be "Not NULL", when a row in its table is inserted generally the default value (usually 0) is inserted into this field. this, to me, totally defeats the purpose of "Not NULL". I would rather have it error out to my application so I can fix the code.
3. What does "Zerofill" mean exactly? Is it the same as making the default value of the field 0?
4. Foreign Key constraint "Cascade on delete" or "cascade on update". I don't quite get how the cascade applies to my database. (it might not) My tables are all fairly seperated by data entity so I don't understand how updating a child row should affect the parent row. (any examples?)
5. char vs. varchar: from what i understand, char takes up more disk space (and memory maybe?) while varchar takes up less space but uses more processing power.
I'm getting to the point where my databases need to be more enterprise worthy and I think this might be a good start for me (and hopefully many others as well)
Bring it on!
Will