1

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

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
MJH
  • 839
  • 1
  • 17
  • 37
  • you will need to more info and narrow options for answerer .Info that would be help us how will the table grow in future,rate of inserts ,selects ,deletes...Unless you are designing tables which hold billions of rows ,i can say you are over engineering. – TheGameiswar Jul 17 '16 at 15:17
  • If I understand you I suggest that there is only need for one table at all CompanyID, ContractID, FieldID – nhaberl Jul 17 '16 at 15:18
  • @TheGameiswar, Company table has around 600 000 records and grows fast (with new inserts all the time.) Contact table is double that, and the related table wont be growing so very much (the field information) because we don't use that info so much. But the info has to be accurate, and we want to make sure duplication is stopped in its tracks. We're trying to clean up a very messy database, that takes up to a minute to run queries on the contacts table!!! – MJH Jul 17 '16 at 15:37
  • I suggest you restructure the question with all the details ,see here for example...http://stackoverflow.com/questions/2320633/first-time-database-design-am-i-overengineering – TheGameiswar Jul 17 '16 at 15:41
  • A table can have only one primary key. Are you suggesting a composite key or unique key? – Gordon Linoff Jul 21 '16 at 16:11

0 Answers0