Database Design Questions
Posted 04 April 2006 - 04:26 AM
uniqueid (auto-incrementing & primary key) - INT
firstname - CHAR
lastname - CHAR
phonenumareacode - TINYINT
phonenumprefix - TINYINT
phonenumsuffix - TINYINT
emailaddress - CHAR
companyname - CHAR
askingprice - INT
listingtype (values are numbers 1-16) - TINYINT
location (2 or 3 letter values) - CHAR
city - CHAR
listingdescription - CHAR
picture (store path) - CHAR
dateadded - BIGINT
Now, all the fields are required except for "picture" and "companyname", so based on a previous post that I read, I guess that:
"If you want a column to store NULL when there is no data then you set it to allow null and the default to null."
So a couple of questions that I have are:
1) Do I need to define default values for all my table fields, or just the ones that don't require a value to be inserted?
2) I'm using phpMyAdmin to do my MySQL work, how do I define the default value to be null? Just type in NULL? I checked the documentation but it doesn't say.
3) I want "uniqueid" to be auto-incrementing and the primary key, so I know that when performing an INSERT, I don't insert a value for this field. But what I want to know is that if this is the value that is returned by the function mysql_insert_id()?
Well, that's about it. I wish there was some more useful information in the phpMyAdmin documentation so that I could figure these out for myself. Oh well. Thanks.
Posted 04 April 2006 - 12:53 PM
2) I strongly recommend that you do not create columns that support the NULL value. I always create NOT NULL columns. There is a speed advantage, and it usually prevents a lot of confusion. The empty string is not NULL, but serves the same purpose. The default will be NULL automatically if your column supports it, or the empty string (or 0 for numeric columns) if you use NOT NULL.
Posted 04 April 2006 - 04:07 PM
Posted 04 April 2006 - 11:05 PM
Many beginners do not realize that there is a difference between NULL and empty(""). An empty string is still a string, it just doesn't have any letters in it. NULL is a special value that is not a string at all! It is there so that if you want to, you can tell the difference between an empty column and a NULL column.
For instance, many people use NULL on integer columns, so that they can have 0 be a valid value, and NULL if there is no value. NULL is not the same as zero.
Overall you are better off ignoring NULL, making all your columns NOT NULL, and treat 0 and the empty string as if they were blank/false/not there/etc. You'll save yourself a few processor cycles and a lot of headaches.
Posted 05 April 2006 - 01:54 AM
Posted 05 April 2006 - 07:12 AM
Usually this comes up whenever a blank string or zero would be perfect valid input for whatever you're storing, and you need to distinguish between this value and someone who's value is unknown. Sometimes it doesn't matter -- like for a middle initial -- but often it does. What if you wanted be to check how many users forgot to enter a particular value?
Just don't write off NULL values all together -- they're quite useful when used correctly.
Posted 05 April 2006 - 03:25 PM
Posted 06 April 2006 - 03:32 PM
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users