|
|
Posted by Norman Peelman on 01/29/07 03:27
Paul Lautman wrote:
> Norman Peelman wrote:
>> Paul Lautman wrote:
>>> 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!
>>>
>>>
>> First of all, my apologies to you, I thought I was replying to the
>> original poster. Secondly, I seem to be the only one offering
>> suggestions. Yes, read up on normalization, get your head spun around
>> with a lot of things you may or may not understand (and I don't claim
>> to). I would imagine the original poster would like to get his
>> database up to speed. Looking at your original post you seem to
>> describe a schema that is the opposite of normalization.
>>
>> For your example:
>>
>>> ID | Film_Type
>>> 225 | Comedy
>>> 225 | Thriller.
>>> This is called "normalisation".
>> ID is not unique and Film_type can (and will) be repeated. And
>> actually both will be repeated quite often as alot of films fit
>> multiple categories.
>>
>> 1NF suggests that ID should be unique (or at least there should be a
>> unique identifier, and there is none) and Film_Type (and ID) should
>> not repeat.
> In this case the primay key would be (ID, Film_Type). 1NF does not require
> that the unique identifier should consist of a single column of a table.
>
Something that you neglected to mention in your original post.
To the original poster, sorry if I gave you wrong advice. You will
definitely need an index on your film_title (whether it be a
multi-column primary or standard index) to have fast searches by that
column.
Norm
Navigation:
[Reply to this message]
|