StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPrimary keys, UUIDs, RecordKey tables, oh my
primarykey
Id
4282519
data
AcceptedAnswerId
4290518
AnswerCount
3
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2010-11-26T04:26:16.390
FavoriteCount
1
LastActivityDate
2010-11-27T06:57:52.717
LastEditDate
2010-11-26T08:36:37.443
LastEditorUserId
409279
OwnerUserId
409279
ParentId
0
PostTypeId
1
Score
2
ViewCount
593
LastEditorDisplayName
text
Body
While working on a content management system, I've hit a bit of a wall. Coming back to my data model, I've noticed some issues that could become more prevalent with time. Namely, I want to maintain a audit trail (change log) of record modification by user (even user record modifications would be logged). Due to the inclusion of an arbitrary number of modules, I cannot use a by-table auto incrementation field for my primary keys, as it will inevitably cause conflicts while attempting to maintain their keys in a single table. The audit trail would keep records of <code>user_id</code>, <code>record_id</code>, <code>timestamp</code>, <code>action</code> (INSERT/UPDATE/DELETE), and <code>archive</code> (a serialized copy of the old record) I've considered a few possible solutions to the issue, such as generating a <code>UUID</code> primary key in application logic (to ensure cross database platform compatibility). Another option I've considered (and I'm sure the consensus will be negative for even considering this method) is, creating a <code>RecordKey</code> table, to maintain a globally auto-incremented key. However, I'm sure there are far better methods to achieve this. Ultimately, I'm curious to know of what options I should consider in attempting to implement this. For example, I intend on permitting (to start at least) options for MySQL and SQLite3 storage, but I'm concerned about how each database would handle <code>UUID</code>s. Edit to make my question less vague: Would using global IDs be a recommended solution for my problem? If so, using a 128 bit UUID (application or database generated) what can I do in my table design that would help maximize query efficiency?
Tags
<database><database-design><indexing><primary-key><uuid>
Title
Primary keys, UUIDs, RecordKey tables, oh my
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USDan Lugg
UserOwnerUserId
1. USDan Lugg
plurals
PostLinksPostIdRelatedPostId
1. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
2. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POPrimary keys, UUIDs, RecordKey tables, oh my
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 POPrimary keys, UUIDs, RecordKey tables, oh my
 UserUserId
 USPhillip
 VoteTypeVoteTypeId
 VTFavorite
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.