|
|
Posted by Paul Lautman on 01/27/07 20:07
Norman Peelman 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
If you read my post you would know that that was not what I was suggesting.
Have you heard of normalisation?
What you are suggesting is the sort of thing I expect to see from a first
year student!
Navigation:
[Reply to this message]
|