[TYPO3-dev] Default definitions of database fields

[Jochen Rau]

Hi.

I will sum up now my and the other participants findings in this thread.

1. Fields with int(11) and int(3) can store the same number range 
(-2.147.483.648 to +2.147.483.647 signed and 0 to 4.294.967.295 
unsigned) and consume the same space (32 bit). The number in braces is 
metadate and only indicates up to how many chars the integer shoud be 
padded with filler chars when shown in terminal view ("         42" with 
int(11)and " 42" with int(3)).

2. It's recommended to add this information to circumvent problems with 
temporary tables in complex joins. The number depends on the maximum 
number of digits plus the sign.

tinyint(n)
8 Bit
-128 to +128 (signed; n=4)
0 to 255 (unsigned; n=3)
smallint(n)
16 Bit
-32.768 to +32.767 (signed; n=6)
0 to 65535 (unsigned; n=5)
mediumint(n)
24 Bit
-8.388.608 to +8.388.607 (signed; n=9)
0 bis 16.777.215 (unsigned; n=8)
int(n)
32 Bit
-2.147.483.648 to +2.147.483.647 (signed; n=11)
0 to 4.294.967.295 (unsigned; n=10)
bigint(n)
64 Bit
-9.223.372.036.854.775.808 to +9.223.372.036.854.775.807 (signed; n=20)
0 to 18.446.744.073.709.551.615 (unsigned; n=19)

3. You should add a NOT NULL statement, if that suits the domain as 
fields allowing NULL can not be indexed and have some other 
disadvantages. But you _can_ allow NULL if that makes sense in the 
domain to be modeled.

4. Add a DEFAULT only if it is necessary. Most DEFAULTS I have found in 
a standard TYPO3 database definition are - ehrm - the defaults of MySQL.

Instead of

sorting int(10) DEFAULT '0' NOT NULL,

you can simply write (and better read)

sorting int(10) NOT NULL,

Unfotunately there is a bug in t3lib_install->assembleFieldDefinition() 
that makes the EM show an update database suggestion on every hit on the 
extension name. I will create a patch for it.

Regards
Jochen


On 11.02.10 17:45, Jochen Rau wrote:
> Hi.
>
> Currently the traditional kickstarter produces default field definitions
> like.
>
> starttime int(11) DEFAULT '0' NOT NULL,
>
> These are used by nearly every extension the last few years. As I write
> the documentation for Extbase now, I want to collect best-practice
> solutions and there came up some questions:
>
> Why do we use (11) after int, tinyint? As this makes perfect sense for
> char and varchar, it is IMO useless for int and tinyint because it does
> NOT indicate how many numbers or bytes the field can hold. It just fills
> the integer up with zeroes on the left. A 42 stored in an int(11) field
> is stored as 00000000042. (Don't ask why "42" ;-) )
>
> Why do we need "DEFAULT '0' NOT NULL"? The starttime set to 0 (unix
> timestamp) means that we start 1-1-1970 (I know that TYPO3 handles 0
> different).
>
> IMO we can write
>
> startime int,
>
> but that isn't possible now, because TYPO3 suggests me his version of
> things every time I hit the Extension in the EM.
>
> Any hints?
>
> Regards
> Jochen