-2

Say I have a JOB and COMPANY table and a third JOB_COMPANY table that relates the two in a many-to-many cardinality.

I've learnt that it's an anti-pattern for the JOB_COMPANY to have a surrogate id PK. The PK should probably be a composite (job_id,company_id).

In this case, following the best practice, how would I sort the JOB_COMPANY by insertion order (and DESCENDING also)? Would this case justify a surrogate id PK?

ijverig
  • 2,795
  • 3
  • 18
  • 26
  • 3
    What exactly is "insertion order"? Tables have no row order, result sets & windows have (partial) order per an ORDER BY. [What is MySQL row order for “SELECT * FROM table_name;”?](https://stackoverflow.com/q/1949641/3404097) [SQL best practice to deal with default sort order](https://stackoverflow.com/q/1793147/3404097) PS 1 DBMS please. 1 question please. – philipxy May 26 '22 at 06:23
  • Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & sometimes limited to titles, but read the help. Google re googling/searching, including in Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy May 26 '22 at 06:23
  • 1
    I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. –  May 26 '22 at 08:10
  • @philipxy I have the need to sort the rows of this relational table by the order they were inserted. In this case, what's the best practice? – ijverig Jun 10 '22 at 18:39
  • Please clarify via edits, not comments. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. PS As the links I gave explain, there is no record of the order in which they were inserted, tables have no row order. PS Ask 1 (specific researched non-duplicate) question. PS [Why is asking a question on "best practice" a bad thing?](https://meta.stackexchange.com/q/142353/266284) [Strategy for “Which is better” questions](https://meta.stackexchange.com/q/204461) – philipxy Jun 10 '22 at 23:02

1 Answers1

0

So its not the end of the world having a surrogate key as a primary key, as long as it isn't a clustered primary key. You want your clustered index to be a composite key in majority of cases as it will usually be how your table is accessed most of the time so you will see performance gains. Unless however you have a table where the data is only accessed by ID, in that case the only choice for the clustered Primary key is ID. What you could have is:

Primary Key NonClustered - ID
Unique Clustered Index - job_id,company_id
OR
Primary Key Clustered - job_id,company_id
Unique NonClustered Index - ID

That way under the hood 2 indexes are created to be used. So you can seek to your data with both of the below predicates

WHERE ID = 
OR
WHERE job_id = 
AND company_id =
sqltechhead
  • 72
  • 4