|
|
Posted by Paul Lautman on 01/28/07 12:09
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.
> This schema in reality is no different than using a SET()
> for Film_Type
this schema is VERY DIFFERENT from using a SET().
> and I would imagine the later is more efficient since
> the SET()
Unless you want to look things up in an efficient way!
> data is stored in the schema definition and only pointers
> are stored in the tables. 1NF suggests that for normalization you
> should have one table for each Film_Type thus ensuring unique ID's and
> non-repeating data.
>
> p.s. Is normalization a requirement?
>
> Norm
Navigation:
[Reply to this message]
|