Posted by Paul Lautman on 01/27/07 12:40

Szymon Bobek wrote:
> Hi!
>
> I have databese with a few tables named like that: films_triller,
> films_horror, films_action etc.
> I decided to create many tables instead of creting just one big table
> containing all sort of films. Is it a good idea? (I think, that it
> boost search time much - am I right?)
> Now - to find a film (when only title is known) I have to search in
> all that tables. But I have a problem with that.. How to do that?
> SELECT * FROM REGEXP 'film_*' WHERE title='the film' does not work.
> The same is when I want to list all tables (to see what groups of
> films I have) - if I try do join SHOW TABLES with REGEXP I get an
> syntax error too.
> Where is the mistake and how to correct it?
>
> Thanks in advance
> Szymon Bobek

Well the first mistake is that you have lots of tables. The way you have is
set up will mean that search times are far greater than they need to be.

Put them all in one table with an ID column. Then have another table
containing columns ID and Film_Type where an ID can be entered many times
each with a different film type. This is because a film can be a
comedy/thriller so if the film's ID was 225, in this table would be entries:
ID | Film_Type
225 | Comedy
225 | Thriller.

This is called "normalisation".

[Back to original message]