Map single element to multiple elements

[russmichell]

Hi there:

I'd like to know if there is a method using MySQL 3.22.xx, of placing multiple data elements in a single field, and being able to do a select on this data?

I don't mean use an ENUM() field as this is either 'A' OR 'B' etc. If anything I need 'A' AND 'B'.

For example:
I'd like to have a subscriber able to subscribe to multiple categories. But I'm not sure how to design the table/s to be able to perform a select for an email address to map to multiple categories.

Can anyone help me out??
Cheers.
Russ

[[deleted]]

Read about database normlization.

Create one table that holds the subscribers, with a subscriber_id.

Create another table that holds categories, with a category_id.

Create another table that holds only a subscriber_id and a category_id.

Create a record in the third table for each combination of subscriber and category that you need.

to get all categories for a subscriber:

SELECT *
FROM subcatlink
WHERE subcatlink.subscriber_id = $subscriber_id

[russmichell]

Cheers for that Vincent.

I like it how you said 'read about normalisation', and then helped me out anyway. You used to just leave it at the 'read about it' bit.

Thanks a lot.
Russ

[[deleted]]

That's only because database normlization is very tricky if you are not familiar with it.

If you had asked 'how do I select only one record' I would have put 'read about LIMIT' :-)

[russmichell]

Vincent:

Thanks for your help, I wonder if I could run my plan passed you which is derrived from your ideas:

My project’s file-system contains x number of directories.
They are dynamically displayed from a database, as categories in the form of checkboxes, where there exists a table for each category.
Checkboxes are checked and an email field is filled out in order to subscribe to a category.
Subscribers emails are inserted as ‘subscriber_id’ into each checked category table.
Using php, the No. Files for each directory are obtained and this is updated as ‘filetotatal’ into the relevant category tables, at time of subscription.
Periodically using cron, get no. Files in each directory & compare with data in each category's ‘filetotal’ field.
Ascertain which directories have been updated (those with higher current filetotals than stored filetotals) something like: If (current total is > stored total) { do something }
Within a loop build a query to include only those updated category names, resulting in something like: “select ‘subscriber_id’ from category01.subscriber_id AND category02.subscriber_id where subscriber_id=’$email’”; - [This will get all the email addresses from each category that has been updated]
Email subscribers informing them of the updated category.
Await next cron job.

Dare I ask what you think? ;-)
Many thanks Vincent.

Russ

[[deleted]]

Looks promising. :-)

So you have this:

main entities: subscribers & categories
(those will become two tables)

Rules:
-One subscriber can subscribe to many categories.
-Each category holds data for a certain directory in the filesytem.

You need one extra table to get the one-to-many connection between the subscribers and the categories.

The categories can all be in one table because it's always the same data, just a different category (a different row in the categories table).

About the updating, I would notify the user whenever the totals change, not just when it increases.

[russmichell]

Many thanks Vincent!

Very useful input - I shall take it on board after the weekend!

Thanks.
Russ