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
need your comments about database of a forum
Moderator: General Moderators
-
php12342005
- Forum Commoner
- Posts: 79
- Joined: Mon Mar 21, 2005 3:35 am
-
php12342005
- Forum Commoner
- Posts: 79
- Joined: Mon Mar 21, 2005 3:35 am
- Maugrim_The_Reaper
- DevNet Master
- Posts: 2704
- Joined: Tue Nov 02, 2004 5:43 am
- Location: Ireland
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.
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
- Maugrim_The_Reaper
- DevNet Master
- Posts: 2704
- Joined: Tue Nov 02, 2004 5:43 am
- Location: Ireland
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
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