-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?

a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
ijverig
  • 2,695
  • 3
  • 17
  • 26
  • 2
    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. – a_horse_with_no_name May 26 '22 at 08:10

1 Answers1

-1

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
  • 62
  • 4