My application contains a table `event` which is essentially a historical log. Currently it contains a UNIQUE KEY on three fields - the location which generated the event `location_id`, the timestamp the event was generated `timestamp`, and the type of event `type`.
I have discovered that this uniqueness guarantee is invalid: some event types have their own type-specific data which is stored in external tables for the event, and this may be required in addition before uniqueness can be guaranteed. The DB design and/or application need to be modified but I'm not sure of the appropriate solution. I must continue to prevent insertion of duplicate events (however 'duplicate' is defined for a particular event type). Possible solutions I have come up with are: 1: Remove the UNIQUE property of the key (I still want it for search indexing) and perform a check for duplicates before INSERT by doing a SELECT for anything that matches the current event. If it's already there, don't insert it. This seems rather crude, and obviously generates much more demand on the DB engine. 2: Create an additional field in `event` which contains any data additional to the current key required to uniquely identify the event. When inserting an event with type-specific data, this field can be derived from teh extra data required for uniqueness, and used in the INSERT - a duplicate key error would then be correct. This still seems a little crude in that the data is replicating that in another table, albeit for a different purpose. Are there any other possible solutions people can come up with? Has anyone had a similar issue before? Many thanks in advance, --Rob ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
