[TYPO3-dev] Why have boolean database fields types like tinyint(4) etc?

[Ries van Twisk]

On Nov 12, 2009, at 2:53 AM, Sebastian Gebhard wrote:

> Hi,
>
> every few days I ask myself why pages.hidden has field type  
> tinyint(4), today it's different - I ask you.
>
> Almost all "boolean" database fields in TYPO3 core and in extensions  
> produced by the kickstarter have tinyint(4),
> an exception is pages.delted which is tinyint(1).
>
> According to the ANALYSE() PROCEDURE of MySQL the optimal field type  
> for these fields would be ENUM('0','1') NOT NULL.
>
> I'm not a database expert, but afaik tinyint(4) requires more  
> diskspace than ENUM('0','1') and is slower.
> Would it not be better to change this?
>
> Kind regards,
> Sebastian


I wouldn't opt for ENUM but keep it as tiny int,
the main problem here is MySQL's odd behavior of the 'BOOLEAN' types  
TRUE and FALSE.
They are constants for 1 and 0. Apparently this is legal in MySQL  
SELECT TRUE + TRUE;
Which is totally bogus and doesn't make sense, code like that could  
even sli`p into extension (I think core people are smarter..)

I would simply accept that  MySQL doesn't have an efficient storage  
mechanism for booleans.

emulating ENUMS in other DB's might be more harder then it sounds like,
special when it comes to modifying enums....



			regards, Ries van Twisk

-------------------------------------------------------------------------------------------------
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS  
WebORB PostgreSQL DB-Architect
email: ries at vantwisk.nl        web:   http://www.rvantwisk.nl/     
skype: callto://r.vantwisk
Phone: +1-810-476-4196    Cell: +593 9901 7694                   SIP:  
+1-747-690-5133