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.

TF_TheMoon

Okay a few things., but first to your questions

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?

If a TABLE has a allow NULL you don't especially need to set a default to NULL, but it helps. If you set defautls to your other required fields then this means if they are unset it will use those default values, which although useful, means you loose the point of having thenm as not null. It means if someone inserts them as null it will pick up on the default. You need to decide what you want to hapepn when someone inserts, say a record with only one of those columns set. If you want it to error, then don't set a default

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.

In the create table statement you'd do something like

Field1 int DEFAULT NULL

Personally, if you wanted to do things visually, i'd suggest using the MySQL AB Query Browser tool or, even better, MySQL Yog.

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()?

If you set the field to AutoIncrement, then yes that will be the value returned by mysql_insert_id()

Right now to your table design

uniqueid (auto-incrementing & primary key) - INT
firstname - CHAR
lastname - CHAR

This looks wrong to me. First up, surname and lastname should be a varchar not a char. secondly make sure to set set the primary key to UNSIGNED so you double the number of postive records you can have against the int. Also do you really need an int? unsigned medium int will give you 0 to 16,777,215.

This look vey wrong to me

phonenumareacode - TINYINT
phonenumprefix - TINYINT
phonenumsuffix - TINYINT

TINYINT signed will be allow records -128 to 127. If you set it as UNSIGNED then you will be able to enter from 0 to 255. Is that going to cover the values you want to enter? Also what if the numbers statr with a leading zero?

emailaddress - CHAR
companyname - CHAR

These must[ be a VARCHAR. Char is fixed length remember, so you'll have to pad or trim records to get them to fit

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

Asking price as an int? You sure you don't want a DECIMAL? INT will not allow floating poin t values and remember that FLOAT, though faster has rounding errors.
Location should be a varchar(3) since you want 2 or 3 characters
City should be a varchar
Description should be a TEXT
Picture (if a text link) shoudl be either a large varchar or a text. If an image it self then a BLOB
DateAdded shoudl be a datetime or timestamp (depending on your version of MySQL)

So to round up those schema changes again

uniqueid (auto-incrementing & primary key & unsigned) 
firstname - VARCHAR
lastname - VARCHAR
phonenumareacode - TINYINT UNSIGNED, better VARCHAR or CHAR
phonenumprefix - TINYINT UNSIGNED better varchar ro char
phonenumsuffix - TINYINT UNSIGNED better VARCAHR or CHAR
emailaddress - VARCHAR
companyname - VARCHAR
askingprice - DECIMAL
listingtype (values are numbers 1-16) - TINYINT UNSIGNED POSSIBLE ENUM
location (2 or 3 letter values) - VARCHAR(3)
city - VARCHAR
listingdescription - TEXT
picture (store path) - VARCHAR or TEXT or BLOBL
dateadded - DATETIME or TIMESTAMP

hadoob024

Cool. Thanks for the tips! A couple of follow-ups to your reply. For all the fields where you recommended VARCHAR instead of CHAR, I only chose CHAR because I was under the assumption that it's faster than VARCHAR. Is this difference not that noticeable where I should just go ahead and use VARCHAR?

Secondly, where you suggested changing "uniqueid" to an unsigned medium int, assuming that I eventually get to 16,777,215 records, will it be easy to change it to unsigned int?

What else, oh yeah, I think you're right about the phone number fields. I think I'm just going to change it to 1 field and make it a VARCHAR. And in my script that adds the info to the db, I'll just convert the 3 different int variables to strings using strval(), and then concatenate them, and store them in the db as a text string. Sound right?

And for "Asking price as an int", yeah, I think we decided to use only whole numbers. I do those checks in the script that processes the form, and only accept whole number integers. I dunno. A business decision that I just went with.

For listingtype, you suggested TINYINT UNSIGNED or POSSIBLE ENUM. What are the possible values for ENUM?

Next to location, where you wrote VARCHAR(3), what is that 3 that you specified? Is that for the size?

For listingdescription you suggested TEXT. This field can only have a maximum of 240 characters. Is TEXT still the way to go (and if so why?) or should I use CHAR or VARCHAR?

And finally, why did you suggest DATESTAMP or TIMESTAMP for the "dateadded" field? What benefit does that offer over BIGINT?

Thanks for ALL the help. Your post was VERY informative!

Sxooter

means you loose the point of having thenm as not null. It means if someone inserts them as null it will pick up on the default.

Note that only mysql does this, and only if you're running without the -ansi switch before version 5.

All other databases, and now MySQL version 5, will error out if you explicitly try to insert a NULL into a column set not null.

This isn't the same, by the way, as leaving them out of the insert list. i.e.

create table test (i1 int, i2 int not null default 22);
insert into test (i1) values (10);

will result in values of 10 and 22 being inserted as of V5.

MySQL, as of version 5, requires you to use the ANSI SQL keyword DEFAULT for that behaviour if you've included the column in the insert list.. Might as well get used to it now...

Phone numbers aren't numeric types. quick, what's aunt bea's phone number divided by 35? If you don't plan on doing math with numbers, then they're really text, like a phone number is. Store it as such.

Everything else is spot on, especially the stuff about chars.

MySQL's changing folks, it's trying to be like the big boys, and that means a lot of weird behaviour (to me, I'm from the other databases camp, seems normal to mysql users) is going to change too.

hadoob024

cool. thanks a bunch man. you guys have really cleared up my questions. aaahhh.... the headache's gone too. quick follow-up. why when creating fields (I'm using phpMyAdmin to do all the work) for the db does it ask for a Length/Values? Like if I define a column to be CHAR, isn't the length set to a range between 0 and 255? I'm not sure I understand why it's asking for this??? Also, I'm not sure if you have any experience using phpMyAdmin, but do you happen to know where I create the users? I know I'm supposed to be able to do this using phpMyAdmin, but I can't seem to find where, and the documentation for it doesn't say either? Thanks again!

Sxooter

Well, char and varchar types normally have a length defined. for char it's the size of the field, and the data will be padded to fill that field. for varchar it's the maximum size of the field, Generally, char is used for fix width fields, like md5 checksums (32) or something like that.

I know nothing of phpmyadmin, as I am a command line user of both mysql and postgresql.

hadoob024

Hmm.... OK. When defining your tables and fields using the command line, if you're creating a VARCHAR field, do you have to define the length of the field? But I think I'm getting it. Like a CHAR has a max value of 255, but if your fields only have a max of like 30 characters, you can specify the length to be 30, right? I guess that makes sense if I'm interpreting that correctly.

Sxooter

yes, when creating a table, you'd do something like:

create table test (info varchar(255), key char(32));

note that a varchar (precision) argument defines the maximum length, but a char(precision) defines the absolute length. ie. the database will right fill the char type to the max size. IF you insert 'abc' into a char(8) you actually get

'abc     '

hadoob024

Oh OK. Cool. That makes sense. And in your example with storing 'abc' in a CHAR(8), when you pull the value out of the db, does the extra space get trimmed automatically? Or is this something I have to do myself?

Sxooter

OK. In any database that is NOT mysql, you will get back an 8 character string. MySQL trims them for you. You can guess which behaviour follows the SQL specification.

OTOH, PostgreSQL has its quirks too. If you concatenate the char value returned to any other text / char type, it trims the char value first, because concatenation is done by text / varchar operators, and has to be coerced first. ugh. So, neither one can claim perfection or anything.

Generally, if you have a field that is ALWAYS a fixed length, use char, otherwise use varchar. The auto-trimming mis-feature of MySQL might lead you to use char where you shouldn't and then you'll find yourself trying to convert your app to run on another database and wondering why it doesn't work.

hadoob024

Cool. Yeah, that makes sense. Here's the final part of my db that I'm still debating. It's for the picture db field. I'm still hearing conflicting things about how to handle this. On one hand, I've been told to store the picture's path in the picture db field, and then storing the picture itself in the filesystem somewhere. And then I've also heard about just storing the image itself into the db and making the picture db field a BLOB or MEDIUMBLOB.

Any thoughts? You see the layout of my db. I'm expecting (more like shooting for) maybe a couple of thousand records, and these records are only going to stay in the main db for 30 days, after which they'll be archived in another db (thanks for the help there too 🙂 ). Thanks.

csn

Here are plenty of thoughts for not storing images in a database:

http://mysqldump.azundris.com/archives/36-Serving-Images-From-A-Database.html

edit - oh, and here's a tip for avoiding hitting Linux's ~32K directory entry limit:

http://37signals.com/svn/archives2/id_partitioning.php

hadoob024

Cool. Thanks for the tip. I didn't even know about the 32k # of file limit for linux. Yeah, I think I'm going to go the storing the image in the filesystem route. Seems like there's way less server and db overhead using that method.

Sxooter

Time has moved a LONG way on since it was necessary to arbitrarily limit the number of files in a linux ext2/3 filesystem directory.

I created a directory with 100,000 files in it and the search time for the OS was .200 seconds for scanning the whole directory. with b-tree indexes enabled, finding individual files was VERY fast, at 0.005 seconds system time. I.e. if you know the name of the file you want, the performance was plenty fast, and likely faster than if you spent your CPU time figuring out what branch of the tree to descend to find it.

Note that file creation time was greater with b-tree directory indexes, but that is to be expected, as you've got a more complex directory structure to maintain.

Sxooter

I just wanted to add something.
If you're letting Windows machines hit your box via Samba, then you want to do what this article says.
I used to have a samba server that was lightening fast. Other linux boxes, accessing it through samba, could scan a single directory with >100,000 files in well under a second.

Windows XP boxes hitting the same directory took about 30 seconds, and thrashed the living heck outta my server.

So, I had to implement the partitioned file layour scheme because the people who write windows don't know as much as about making things go fast as the guys from Australia who hacked the network file system it uses. kinda funny really.

csn

Sxooter - I think it's more an inode variable set at kernel compile time rather than a filesystem issue. On my Redhat 9 box it definitely wouldn't store more than 32K (or maybe it was 40K) entries per directory. Haven't tried on my FC4 machine.

Sxooter

Huh. That's odd. I had a bog standard linux kernel in my RedHat 7.2 server, and it's the one I mentioned above as having 100,000 (actually a little more than that) files and no problems. Of course, that wouldn't be the first time RedHat moved backwards whilst moving forwards either.

hadoob024

Hmmmm... Hopefully this won't be an issue for me. If I'm hitting anywhere the number limits per directory that you guys are talking about, hopefully I can retire and let someone else deal with it 🙂

It's definitely good to know though!

hadoob024

I have a quick follow-up to my original question regarding design. So basically, I need to figure out a good way to deal with giving pictures a unique name before storing it in the filesystem and storing the path in the db. Here's what I was planning on doing:

1) Add lisiting info to db using INSERT command

2) If $uploadsuccess === 1, that means we uploaded a pic, so if previous INSERT was successful, get ID generated from previous INSERT operation using mysql_insert_id()

3) Move uploaded file to directory, and give it a name using ID from mysql_insert_id() as the base of the filename

4) Add filename/path info to db to previous entry using UPDATE

Is this how people handle this situation?

csn

Sxooter wrote:
Huh. That's odd. I had a bog standard linux kernel in my RedHat 7.2 server, and it's the one I mentioned above as having 100,000 (actually a little more than that) files and no problems. Of course, that wouldn't be the first time RedHat moved backwards whilst moving forwards either.

On my new FC4 box /tmp grew to over 100K files, so it appears the 32K limit doesn't apply. Yay! I wonder if there is any limit, absolute or practical...