1

I am building the database for a web application where users may submit data. Each datum is unique, and more than one user can submit the same datum.

It is important, from the application's standpoint, to know the order in which users submitted the datum.

I have made a table exclusively for this purpose. It has the following fields:

SubmissionID
UserID
SubmissionOrder
... # and other non-important ones

My question is, which attributes should I make primary keys?

SubmissionID and UserID would allow for duplicate SubmissionOrders for a (SubmissionID, UserID) pair.

SubmissionID and SubmissionOrder would allow the same user to submit the same thing twice.

UserID and SubmissionOrder... would limit the user considerably in terms of what he can submit :P

All three would allow duplicate SubmissionOrders for different UserIDs.

Is there another solution which I am not pondering?

Is this problem better solved at the application level? With triggers?

Thank you for your time!

PS: Some technical details which I doubt you'll find useful:

  • The application is written in PHP
  • The database runs on sqlite3
F. P.
  • 5,018
  • 10
  • 55
  • 80
  • I think you've made the question a bit too vague, possibly by trying to leave out domain details. It's difficult to model something so vague (there's data, and I need it in order, and these columns - which aren't helpfully defined - don't seem to do it for me). – Mark Brackett Oct 11 '11 at 21:45
  • @MarkBrackett - I'm sorry. What else would you need to know about it? User submits piece of data. Another user submits same piece of data. I need to know the order in which the users submitted said piece of data. – F. P. Oct 11 '11 at 21:57

3 Answers3

1

In regards to your specific question :

It is important, from the application's standpoint, to know the order in which users submitted the datum

I think there are 2 alternative options than a combined field primary key:

(1) Create an additional column - a single integer (auto-increment) primary key.

or

(2) Create a timestamp field and save the date/time the data was input.

Barry Kaye
  • 7,682
  • 6
  • 42
  • 64
1

The order in which things happen is just a little fuzzy on most SQL platforms. As far as I know, no SQL platform guarantees both these two requirements.

  • There must be no ties.
  • Earlier submissions must always look like they're earlier than later submissions.

With a timestamp column, earlier submissions always look like they're earlier than later submissions. But it's easily possible to have two "submissions" with the same timestamp value, no matter how fine the resolution of your dbms's timestamp.

With a serial number (or autoincrementing number), there will be no ties. But SQL platforms don't guarantee that a row having serial number 2 committed before a row having serial number 3. That means that, if you record both a serial number and a timestamp, you're liable to find pairs of rows where the row that has the lower serial number also has the later timestamp.

But SQLite isn't quite SQL, so it might be an exception to this general rule.

In SQLite, any process that writes to the database locks the whole database. I think that locking the database means you can rely on rowid() to be temporally increasing.

So I think you're looking at a primary key of {SubmissionID, UserID}, where rowid() determines the submission order. But I could be wrong.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
1

So you have the submitted data (SubmissionId?), which you want to allow duplicates on (so that another user can submit duplicate data), but not duplicates for a single user. That calls for a unique constraint on (SubmissionId, UserId).

Your next requirement is that you "know the order in which users submitted the datum". It's unclear if that's true for all submissions, or only the submissions that have duplicates*. Solving the general case (all submissions) solves the specific - so we'll go with that.

Since this is an ordering problem, something SQL doesn't really deal with, you'll need to add an attribute that will give you absolute ordering. The 2 standard choices are an autoincrement, or a timestamp. We'll pick an autoincrement so that you don't have to worry about ties. I assume that SubmissionOrder is your placeholder for that. Since we used an autoincrement column, it's guaranteed to be unique. So, we have another unique constraint on (SubmissionOrder).

These unique constraints are now your candidate keys. Pick one as your primary key, and use a unique constraint for the other.

* Your comments about duplicate SubmissionOrder confuses the issue a bit - suggesting that SubmissionOrder is only unique to a SubmissionId. Assuming you have application logic to create the next SubmissionOrder, this is a valid (though slightly harder) alternative. Your unique constraint would then end up being on (SubmissionId, SubmissionOrder).

Mark Brackett
  • 84,552
  • 17
  • 108
  • 152