[Typo3-dev] RFC: TYPO3, Kickstarter and M-M database relations

[Arne Skjaerholt]

> For example I have this list in a record:
> 345,6,17,231
> How would I do a like query on this one to look for number for UID 1?

Now that you mention it, I remember that I didn't use LIKE, LIKE
couldn't do that particular task, so I used REGEXP instead (I think
that's a MySQL extension tho'). I just looked at my code, and here's how
I did it:
SELECT field FROM table WHERE comma_field REGEXP '(^|,)$uid(,|$)' 

It's kinda ugly, but it does the job.

> Also, a query like this: LIKE ('%somedata%') cannot be optimized at the 
> DB level
> because the RDBM cannot do a index scan but rather have to do a 
> tablescan, means has to scan ALL records!
> 
> I would love to see MM relations,
> unfortunatly I don't think that's possible anymore with typo3....
> 
> I have seen however that Kasper did some speed measurements and found 
> that on mysql there is
> hardly no speed differences and so decided not to go for a relational DB 
> setup.

The speed difference being negligeable is good at least, but there are
two reasons I don't really like the current solution:

1) It's poor DB design. In fact, it's the kind of DB design I'd expect
from "home-brew" software, not professional grade software.

2) Certain operations that could be done with a JOIN in a proper
MM-relation enviroment are simply impossible to do without first
selecting the usergroups field of a user (to take the example I worked
with), splitting the string into an array, and then executing a second
query to get the data you were looking for.

Arne
:wq