Comment by forkqueue | Hacker News Reader

Comment by forkqueue | Hacker News Reader

A couple of points for those thinking about doing something similar:

"Since InnoDB stores the table in the primary key, I decided that rather than use an auto_increment column, I'd cover several columns with the primary key to guarantee uniqueness. This had the added advantage that if the same record was inserted more than once, it would not result in duplicates."

The 'correct' way to deal with this in MySQL is using the auto_increment_increment.

http://dev.mysql.com/doc/refman/5.0/en/server-system-variabl...

Of course, the real difficulty with mutli-master setups split across data centres isn't ensuring uniqueness of primary keys, it's ensuring data-integrity under a split-brain scenario, i.e. where one server can't reach the other, but users can reach one or the other. UPDATEs and DELETEs to rows can then become extremely difficult to merge back together. This wasn't a problem for this application, but as others have commented, this use case probably wasn't best suited for an RDBMS anyway.

with an autoincrement id, duplicate rows may get inserted. having a primary key derived from the data results in duplicate rows getting discarded (this is important).

secondly, the autoincrement id adds 4 bytes to each row which are never used for anything. only use an id if you need to reference a row from another table.

duplicate rows may get inserted

Make that duplicate rows WILL get inserted -- even if only due to network glitches causing connections to die after the database adds the row but before the client receives the acknowledgement resulting in the client retrying the request. Unless you don't retry failures, in which case you lose rows instead, of course.

That's a good warning sign that the data isn't relational in the first place. An RDBMS would probably be a good fit for the analyzed data (which is likely to have explicit relations), though.

No it's not. There are plenty of tables that use composite keys that also contain foreign keys.

The use of ORMs like active record, many of which choke on natural keys, has turned a lot of devs into automatons for artificial key creation. Natural keys are often superior.

Reply on news.ycombinator.com