Jump to content

Database Design Questions


hadoob024

Recommended Posts

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.