|
|
Posted by strawberry on 01/27/07 17:18
On Jan 27, 3:55 pm, Norman Peelman <npeel...@cfl.rr.com> wrote:
> Paul Lautman wrote:
> > Norman Peelman wrote:
> >> 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
>
> >> Your structure is fine... the problem is your method of searching. I'm
> >> not really sure you can use REGEX in that context:
> > You REALLY think that structure is fine??? What do you THINK is wrong with it? If you want to go for one big
> table then your structure could become:
>
> film_type set('horror','thriller','comedy','romance','action',...)
>
> and then a row could be:
>
> film_id film_title film_type
> 1 Romancing the Stone action, comedy, romance
> 2 GODZILLA action, sci-fi
> 3 Jurasic Park action, sci-fi
>
> and your search(s) becomes:
>
> SELECT * FROM film_table WHERE film_title LIKE '%Jurasic%'
> returns data on Jurasic Park
>
> or
>
> SELECT * FROM film_table WHERE FIND_IN_SET('sci-fi')
> returns data on GODZILLA and Jurasic Park
>
> ...does that look better. (an ENUM can contain only one item from a
> list, a SET can contain multiple item from a list)
>
> You would still want an index on the film_title. Not sure if you can
> index a SET column but you could try.
>
> Norm
NO!
Read up on normalisation.
Navigation:
[Reply to this message]
|