Database Design Questions

[hadoob024]

OK. I've been messing around with PHP now for a bit and I think I'm getting comfortable with it, so now I'm ready to implement the database that I've designed. It's for a real estate site, so here're the fields that I have and the data types that they're gonna be:

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.

[wickning1]

1) Default values will be the empty string, 0, or NULL, depending on your column type. You can specify a value for convenience, wherever it makes sense.

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.

3) Yes

[hadoob024]

Cool. Thanks! A couple of follow-ups to your replies. So how do I define the empty string value, just leave it blank and do anything, and MySQL interprets this as an empty string? Also, instead of allowing NULL values, where a value isn't entered in my form, should I just store a simple text string in the db for that field like "nothing"? And then when searching on the db, I check the value of this field, and if it's equal to "nothing", I just ouput blank space? Thanks a bunch!

[wickning1]

All of the empty string stuff will be automatic. The empty string is simply "". If a PHP variable is empty, it doesn't print anything.

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.

[hadoob024]

cool man... thanks for the tips. yeah, i think i'll end up going with the NOT NULL value, and if for one of my fields nothing is entered, i'll just store "" in the db for that field value. thanks again..

[fenway]

While NOT NULL by default is generally a good rule-of-thumb, you shouldn't be afraid to use it whenever necessary -- it's there for a good reason.

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.

[hadoob024]

oh no, i wasn't just writing it off altogether. just for my situation in particular. i don't need to know or keep track of users who don't enter in information for the two fields that i'm talking about. so if they don't enter anything, i'll just store an empty string. that sounds right, right?

[fenway]

Sounds good to me.

[hadoob024]

Cool! Hey man, THANKS for all the help!!! Now that I know what I'm doing, I think I'm ready to get started.