I'm designing a new database model, and I'm debating about how to set up primary keys for tables.
If I give a table 2 primary keys, it most certainly helps with data integrity to make sure no duplicates are entered from any direction. So if I have an associative table with CompanyID and ContactID as my primary keys (I need a many to many relationship between company and contact,) if I want to now relate this table to another table where this contact works for this company in a certain specific field (and I have another table with all the various field types,) does my related table now have 3 primary keys with CompanyID, ContactID and FieldID.
The other option is what I am more used to, and that is giving my associative table its own id field which will be the primary key, and then having that as the foreign key in my new table. For integrity, the second option seems to be a con, but I seem to remember reading somewhere that making more than 1 primary key for a field will slow down queries.
What is the best way to go when speed is an important factor, not only for selects, but also for inserts and updating?