Mysql primary and unique key (prevent duplicate data)

Question

I have this scenario and am trying to figure out how to stop the insertion of duplicate data into the db.

I have table log and include row:

device id , time (this is not the time insert but its the time the logs occur) , content

Each user can submit from their device id with data example

123ABC , 2013-11-27 01:19:37 , sample text

Each user can submit up to 500 logs from a device using xml protocol. So from control panel page, I can select user (from this device id) and viewing total of logs they submit (distinct device id, time) so I only see their unique logs, for example, 400 unique logs. But in the back end mysql they have up to 2000 record logs and all of them duplicated 5 times from 400 unique logs.

• I can't make device id as unique because 1 device can submit multiple times and it will stop insert data because each time can be same device id.

• I can't combine unique (device id and time). i thought this would work but the data won't insert at all; it rejects my query. Each data have unique time, content but not device id...

How can I make this work?

If I want each user to continue submit data logs, but not duplicate ones and only unique data logs from a same device id.

If I select Time and Content only as unique index. It will fail sometimes if multiple device submit same time of logs has occur and content is the same...

How can I make it unique for each device id but not other device id.

Answer 1

try like this:

• create table with fields batch_id and device_id
• create MD5 hash from data user submittes (whole xml)
• add record to created table with that MD5 hash and device id

then you can just check if content is already submitted before by checking content hash against database hashes.

Answer 2

I can't see why a regular composite unique index on both columns wouldn't work;

CREATE TABLE logs (
  log_id INT AUTO_INCREMENT PRIMARY KEY,
  device_id  VARCHAR(32),
  time       DATETIME,
  content    VARCHAR(32)
);

CREATE UNIQUE INDEX uq_logs ON logs (device_id, time);

An SQLfiddle to test with.

-- First insert
INSERT INTO logs (device_id, time, content) VALUES 
  ('123ABC', '2013-11-27 01:19:37', 'sample text');

-- New time, ok    
INSERT INTO logs (device_id, time, content) VALUES 
  ('123ABC', '2013-11-27 01:19:47', 'sample text');

-- Duplicate of #1, fails
INSERT INTO logs (device_id, time, content) VALUES 
  ('123ABC', '2013-11-27 01:19:37', 'sample text');