I am currently in the process of designing a database solution that requires
columns to have array-like capabilities. To provide the group with a better
notion of what I am trying to accomplish, please review the following
example.
Let's say that in a particular calendar application, a user wishes to be
notified by e-mail a certain number of times about a future event. This
would require a field of type "datetime" in order for a server-side daemon
to determine when it should initiate the notification message. However,
while one individual might want to be notified about an event only once,
another may wish to have five or even ten notifications thereby resulting in
superfluous allocations for the more conservative user (i.e., columns that
are never filled).
It is my understanding that there are several methods that can easily
correct this problem. First, if the date field was to be changed from a
"datetime" to "text" type, the multiple times could be stored in a
improvised datatype (e.g., separating each item by a "|"); but this is
inefficient, given that it will [hopefully] be the authority of the MySQL
database to identify those records corresponding to events that need
notifying (e.g., SELECT * FROM event_table WHERE date_field <= now() &&
notified = 0) for otherwise the data transfer could be overwhelming and the
code processor-intensive. Second, an arrangement with a second table could
be created with the following format:
notify_table:
user_id|event_id|notify_datetime|notification_sent
In this manner, the background daemon would query the notification-data-only
table for all events that have notification times less than or equal to the
present ( SELECT event_id FROM notify_table WHERE now() >= notify_datetime
&& notification_sent = 0 ) and given the <event_id>s returned, identify the
proper user and corresponding event. While this is the most
architecturally-sound possibility that I am familiar with, it would be more
convenient to retrieve an event's contents in the same query that identifies
those items which need to initiate notification. Thus the original
question: is an array-like data type possible in MySQL, and if so, how?
Thank you in advance for your support!
Regards,
Brad Galiette
---------------------------------------------------------------------
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
