data type

[javaPete]

I'm creating a database table that will contain user name and contact information. I have question about what is the correct data type to use for each field. I've read mysql handbook, but what it doesn't explain is how number of characters works with numeric types. Its obvious that char(2) is a two chacter string but what is TINYINT(3) and what effect would signed or unsigned have on it? What woud the result be of a SMALLINT(2)UNSIGNED ? Would it only accept values from 0 to 99 ?

For example, if I want a field to store a user's zipcode what datatype should i use and should i specify the number of characters? Since, zipcode is five digit would SMALLINT[(M)] Unsigned [0 to 65,535] not work because if a zip was over 65535 it wouldn't work.

What data type should i use for a phone number?

so far this is what i have:

id_user - SMALLINT AUTO_INCREMENT
email - VARCHAR(30)
firstname - VARCHAR(15)
lastname - VARCHAR(20)
phone - INT(7)
address - VARCHAR(200)
city - VARCHAR(30)
state - CHAR(2)
zip - SMALLINT(5)

[raf]

http://www.mysql.com/doc/en/Column_types.html
TINYINT(3) and what effect would signed or unsigned have on it?
the (3) means that three positions will be shown . Doesn't says anything about the actual stored valies. But you best set it large enough so that the whole value is displayed (unless for char and varchars)
The valueranges can be picked up in the above url
For example, if I want a field to store a user's zipcode what datatype should i use and should i specify the number of characters? Since, zipcode is five digit would SMALLINT[(M)] Unsigned [0 to 65,535] not work because if a zip was over 65535 it wouldn't work.
It postcodes are always numeric, and you use them to select on (veryimportant differnece) or as a field to link to other tables, then they should be mediumint.
If you don't use it to select on or link with, you can just as well use a varchar(5) --> can hold <= 5 characters
What data type should i use for a phone number?
the same. But you probably wount selecy on it or join with it, so take a varchar(x)

[javaPete]

Ok it makes sense that numeric data types are more efficient when used to join or select. What advantage would there be to storing numeric data in a string data type? Wouldn't it open up the posibility for a user to enter incorrect type of data if validation didn't catch it?

[raf]

What advantage would there be to storing numeric data in a string data type? Wouldn't it open up the posibility for a user to enter incorrect type of data if validation didn't catch it?
Well, as long as you are absolutely sure that all values will be numeric you could store them in numerical field.

But in most cases, you'll sooner or later get an exception. Imagine they split up a postcode into 25448A and 25448B, then you would need to switch to a varchar AND you'd need to change all your statement (add quotes around the values)

If it's just to store and retrieve the values, then there isn't much differnce. And you probably wount be joining or filtering on them. Even for filtering, the value-frequences will be that low (almost unique values ?) that indexing them would be a waste of space anyway so that's no reason either to go for numericals.

As for incorrect data
--> hmm. Don't know how mySQL handles scientific notations, but you might be able to slip in a 'E' or so
--> you should do a better formvalidation (just a simple is_numeric() will do). It is far easier to do this before the insert + it should be done there so that you can reload the form and give an adequate and fieldspecific errormessages.
If you insert a wrong value, then mySQL wount always throw an error and even f you get an error, how are you going to roll everyhing back and reload the form with an errormessage?
--> always code as defensive as possible ==> trap the error as soon as possible, with an explicit control that you fully tested, instead of relying on some other tools build in errortrapping. mySQL should only slightly change it's errorhandling or the config settings should only be a bit different on some server you migrate to, and you'll get into trouble.

[javaPete]

raf thanks for the advice. I'm building a simple database for my freelance site to automate the process of design quotes for potential customers. My idea is:

1. customer signs up for an account
2. verification email is sent with their name and password.
3. customer logs in fills out job request questionnaire.
4. request email is set to me.
5. I reply with job quote.
6. (not sure if the customer should have the ability to edit and resubmit request or I'll add this functionality later on.)

So I envision 2 tables:
1. holds user name, password and contact info.
2. stores data from job questionnaire form.

Not sure if I'm making this process too complex. Maybe a potential customer should only needs to give me his/her email and fill out request form without having to go through the hassle of signing up and entering details such name and address. What do you think?

[raf]

Not sure if I'm making this process too complex. Maybe a potential customer should only needs to give me his/her email and fill out request form without having to go through the hassle of signing up and entering details such name and address. What do you think?
As a client, i would think : woaw, does het get that much quoterequest that it needs to be automised !

I think a simple contactform would do to get in contact with them, and from then on i think a more personal approach would work better for me (just me ...)

Problems of course, will be all sorts of idiots filling in bogus forms. So it's basically the tradeoff between userfriendlyness and keeping the junk out.

As for the db-design: i would add a third table that records the IP, sessioninfo etc. Just to track down and discourage the occasional joker.

My contactform would also be quite structured, so i would register the details in a 'factstable' and then have a bunch of dimenbsiontables containing the options etc from my contactform dropdowns etc. But i'm used to making everything maximaly db-driven, so this might be a bit overkill for your situation.

[javaPete]

Well the reason for the request form automation is two fold:

1. I wanted to create database project that I would learn from and achieve a result I could use. I also like showing the customer what I'm capable instead of listing it on my resume.

2. I often find myself repeating the same questions to each new customer. It's more of a tool to get the client to think of the type of information I need to design the site. Its often a lengthy process of back and forth emails trying to understand what a client really needs. I guess it's more efficient to structure the dialog.

The more I think about it the less suitable a database solution seems for a questionnaire. It's very likely that the questions on the client request form will change. If for example I need to remove a question, I will not be able to delete it from the database since it would affect prior forms stored there.

The ideal solution would be pdf document they could fill out, edit online, download, and print out. But from what I've read editing functionality for pdfs is cost prohibitive. Php provides for free on the fly pdf creation but not editing. It seems like emailing them a word document questionnaire, while low tech is the easiest solution. But you still have to rely on the client having MS word to edit it.

[raf]

The ideal solution would be pdf document they could fill out, edit online, download, and print out. But from what I've read editing functionality for pdfs is cost prohibitive. Php provides for free on the fly pdf creation but not editing. It seems like emailing them a word document questionnaire, while low tech is the easiest solution. But you still have to rely on the client having MS word to edit it.

I'm currently working on an aplication that does exactly that. It lets you define forms (setting up validationrules, specify evaluationrules, computationrules etc) and outputformats (including dynamical PDF creation). These are the adminfunction.
The regular webuser can the load the forms, fill them in, have them validated and computed and then gets a pwd-protected pdf with the processed questionairs info in it.
The forms can be reopened and edit on line. After which a new PDF can be created. (+ opened or mailed to the user)
So the user only needs an acrobat reader.

It's first implementable version should be ready by the end of this week. As a spinoff, my client and I are planning to offer a webservice where you can create the PHP code to dynamically build the pdf's at runtime (including the dataselection and integrating that into the pdf-file.)