need your comments about database of a forum

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
php12342005
Forum Commoner
Posts: 79
Joined: Mon Mar 21, 2005 3:35 am

need your comments about database of a forum

Post by php12342005 »

assume a forum (similar to this one) database contains 3 fields (in one table):
1. user ID (20 characters)
2. title of the post (50 characters)
3. content of the post (10 - 10,000 characters).

my question is about field 3:

this field varies from 10 to 10,000 charecters, if the field is created with a fixed size (text, length is 10,000), it must vaste a lots of space because in most cases the length of a post is less than 10,000 characters.

how do you solve the problem in db designing? use "dynamic length field" or something else?

thanks
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

phpBB just uses a TEXT field
php12342005
Forum Commoner
Posts: 79
Joined: Mon Mar 21, 2005 3:35 am

Post by php12342005 »

is the length of the TEXT field of thr phpBB fixed?????

how long? 10,000 characters?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

TEXT has a maximum of 65535 characters.

You dont need to specify a length
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post by Maugrim_The_Reaper »

TEXT meets your needs for scaleability, as far as I recall the storage requirement for TEXT is the length of the contents plus 2 or 3 bytes. Storage for most database fields is similar in thought - varchar(128) is not going to consume 131 bytes if empty or only partially filled.

Storage is a function of length - not maximum length for a given field type.
php12342005
Forum Commoner
Posts: 79
Joined: Mon Mar 21, 2005 3:35 am

Post by php12342005 »

what do you mean?
the space or size used by database for the TEXT is dynamically changed according to actual length of the TEXT?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the physical space used will vary for each record based on length and compression of that field's data. TEXT has a maximum capacity of 65,535 bytes (64K). MEDIUMTEXT has 16,777,215 bytes (16MB). and LONGTEXT supports up to 4,294,967,295 bytes (4GB). Note: these numbers are the decompressed size.
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post by Maugrim_The_Reaper »

Not exactly - but close enough.

You seem to assume that giving a field a length of say, 255, means it requires 255 bytes of space to store. The reality is that it requires a few bytes plus the length of the actual content.

Storing an identical string in varchar(128) and varchar(255) will take the same storage space. The unused max length requires no storage in and of itself.

You can use TEXT safe in the knowledge that it will only use the storage space necessary for the contents of each field...

Here's the reference for the manual: http://dev.mysql.com/doc/mysql/en/stora ... ments.html
Post Reply