I'm building the database for any web application where customers may submit data. Each datum is exclusive, and most one user can submit exactly the same datum.

It is necessary, in the application's perspective, to understand an order by which customers posted the datum.

I've designed a table solely for this function. It's the next fields:

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

My real question is, which characteristics must i make primary secrets?

SubmissionID and UserID would permit duplicate SubmissionOrders for any (SubmissionID, UserID) pair.

SubmissionID and SubmissionOrder allows exactly the same user to submit exactly the same factor two times.

UserID and SubmissionOrder... would limit the consumer substantially when it comes to what he is able to submit :P

The 3 allows duplicate SubmissionOrders for various UserIDs.

Can there be another solution which I'm not pondering?

Is problem better solved in the application level? With triggers?

Appreciate your time and effort!

PS: Some technical particulars that we doubt you will find helpful:

  • The applying is designed in PHP
  • The database operates on sqlite3

When it comes to your particular question :

It is necessary, in the application's perspective, to understand an order by which customers posted the datum

I believe you will find 2 alternative options than the usual combined area primary key:

(1) Create one more column - just one integer (auto-increment) primary key.

or

(2) Produce a timestamp area and save the date/time the information was input.

An order by which unexpected things happen is simply a little fuzzy of all SQL platforms. So far as I understand, no SQL platform guarantees these two two needs.

  • There has to be no ties.
  • Earlier distribution should always seem like they are sooner than later distribution.

Having a timestamp column, earlier distribution look like they are sooner than later distribution. But it is easily easy to have two "distribution" with similar timestamp value, regardless of how fine the resolution of the dbms's timestamp.

Having a serial number (or autoincrementing number), there won't be any ties. But SQL platforms don't guarantee that the row getting serial number two committed before a row getting serial # 3. This means that, should you record both a serial number along with a timestamp, you are prone to find pairs of rows in which the row which has the lower serial number also offers the later timestamp.

But SQLite is not quite SQL, so it may be the best for this general rule.

In SQLite, any procedure that creates towards the database locks the whole database. I believe that securing the database means you are able to depend on rowid() to become temporally growing.

And So I think you are searching in a primary key of , where rowid() determines the submission order. However I might be wrong.