On Thu, 29 Nov 2001, Graham Nichols wrote:
> 'category' can be made up as records such as:
>
> christmas|birthday|easter
> easter|thanksgiving
> birthday|christmas|thanksgiving|easter
The standard way to deal with this kind of problem is to create
a table modelling the event entity set and one relating event
to category number.
Table Belongs (this models a relationship set):
PRIMARY KEY (catgory_number, event_number)
FOREIGN KEY (catgory_number) REFERENCES Events
catgory_number | event_number
------------------------------
1 | 1
1 | 2
1 | 3
2 | 3
2 | 4
3 | 2
3 | 1
3 | 4
3 | 3
Table Events (this models an entity set):
PRIMARY KEY (event_number)
event_number | event_name
---------------------------
1 | christmas
2 | birthday
3 | easter
4 | thanksgiving
Then for example to display all events in category one you would do:
SELECT event_name
FROM Events, Belongs
WHERE Events.event_number = Belongs.event_number
AND
catgory_number = 1;
Note that FOREIGN KEYs are not supported by mysql but the syntax is
supported hence you can still code them in. This lack of functionality
should not cause you any problems. The tradeoff for this loss is a gain
in speed.
Include the event_number in any table that uses an event if any.
Regards and have fun! (p.s. I leave the CREATE TABLE statements as an
exercise for you).
Neil
> etc .....
>
> As you can see, items are delimited with a '|' in the fields. If I wish to
> select all records which contain 'christmas', what is the correct sql syntax
> please?
> (This should return records 1 and 3 from the example shown above)
You could also go WHERE X LIKE "%christmas%" but this is a HIGHLY
INEFFICIENT way of dealing with this problem.
> I've tried several approaches, but have failed so far.
>
> Thanks for any pointers.
>
> kind regards, Graham Nichols
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
