Suggestions on field sizes and datatypes

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Suggestions on field sizes and datatypes

Post by raghavan20 »

what do you think shd be the datatype and size for following fields?

So far, I have been doing to my own discretion but from now on I want to identify best practices and I dont wanna touch the db later on which is a very bad idea as we know.

1. FirstName - datatype if not text, wots the size in varchar?
2. LastName - datatype if not text, wots the size in varchar?
3. Password?
4. OrderId?
5. SessionId?

I used to store the address as:
Street, City, State, Country, PostCode.
what do you suggest on the names of the fields and their datatypes and sizes?

Do you think its gd to capitalize fields in tables like FirstName instead of firstName or firstname?

what are the significant advantages of 'text' and 'varchar' datafields one over the other?
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

Yo,

VARCHARS/ CHARS - word, alpha numerics, not more than 225 characters in text
TEXT/ BLOB - description, multiline, image data
INT/ INTERGERS - autoincrements, digits, numbers * esepcially if u need numeric sorting

http://www.mysql.com - information on mysql, data types, column types, a whole bunch of other stuff :wink:
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Suggestions on field sizes and datatypes

Post by timvw »

FirstName CHAR(20)
LastName CHAR(30)
Password CHAR(30)
SessionId CHAR(32)

So users can have only one session at a time? How does it look like?
Wtf is an orderId? What is it supposed to do? How does it look like?
what do you suggest on the names of the fields and their datatypes and sizes?
Do you think its gd to capitalize fields in tables like FirstName instead of firstName or firstname?
There are way too many differences between DBMS products that are case (in)sensitive... I adhere the KISS principle and therefore choose to have them all lowercase..
what are the significant advantages of 'text' and 'varchar' datafields one over the other?
The advantage of VARCHAR over CHAR is that if you have all firstnames that are only 8 chars longs, you won't spill diskspace on the remaining 12 (20-8=12)

The advantage of CHAR over VARCHAR that searching can be faster because the fixed width of the field...
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

I dont know the length of the sha256(password) and I store it in text
Post Reply