Database Theory Questions/Discussion

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Database Theory Questions/Discussion

Post by waskelton4 »

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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Database Theory Questions/Discussion

Post by Weirdan »

waskelton4 wrote: 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.
http://dev.mysql.com/doc/refman/5.0/en/ ... #id2943553
As of v5.0.2 you may tweak your db server to reject such invalid statements.
3. What does "Zerofill" mean exactly? Is it the same as making the default value of the field 0?
it means: when returning numerical data, pad it to field length with zeroes:

Code: Select all

mysql> create table a(b int(4) zerofill);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into a values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into a values(1234);
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+------+
| b    |
+------+
| 0001 |
| 1234 |
+------+
2 rows in set (0.00 sec)
First record comes out right-padded with zeroes
Post Reply