CODEWITHSUNDEEP
sql-serverdatabaseentity-framework
MJH
MJH

Reputation: 859

Setting the correct primary key for tables in a database model

I'm in the process of making a new entity framework model to store our data in a much cleaner and more accurate way. Our present database (in Access) is full of duplications and unreliable data and was not planned by a database expert, so is often missing primary keys altogether. It is also very large, so queries take a very long time (even with indexes.)

In the new database model, I want it to allow for speedy inputs for one department of employees, as well as speedy selects for everyone else. We have tables with over a million records, not to mention audit tables, which are much bigger of course.

My model has a company table, with companyID as its primary key, a contact table with contactID as its primary key, and then an associative table called CompanyContact which has both companyID and contactID set as its primary keys. I then need to relate other tables to this associative table. For example, if I want to store interactions between employees and contacts (who may work for more than 1 company at the same time,) I could set the primary keys as companyID, contactID and EmployeeID, for this interaction table, but I'll have 3 primary keys. (I have numerous other scenarios of this question in the database.)

I recall reading (somewhere...) that giving more than 1 primary key to a table will slow down queries. They recommended giving (as appropriate to the above example) the CompanyContact table an id field as a primary key, and 2 foreign keys (CompanyID and ContactID,) and then relating the interactions table to that id field. I'm more used to doing it the way they recommend, but have seen that making multiple primary keys out of the foreign keys stops duplication at the root, without having to code it in anywhere.

Can anyone tell me just how much having multiple primary fields slows down queries and what the most recommended method is? Thanks in advance!

Upvotes: 0

Views: 123

Answers (1)

Joe C
Joe C

Reputation: 3993

You are considering a composite primary key vs. a surrogate key. There are many debates on this topic.

Composite key has all fields that make the row unique and can work well if they are all int or bigint and will not work as well if they are larger (ie varchar) fields. Also the key length has a maximum that you have to stay under.

The surrogate key approach would likely have an auto increment integer and the key fields would have a unique index on them to prevent duplication.

Regarding employees who work for multiple companies, it may be best to consider the employee at company A as a separate entity from the same employee at company B. If not I would go for a somewhat more complex design. A persons table, an employee table where the same one person record would relate to possibly many employee records. Each employee record tying to a company based on an employment 'contract'.

I would do the same to Contacts. This way your communication design is streamlined and concise while still enabling queries that will identify the same 'person' from multiple companies (vendors, clients, etc)

In this way the table you mention would only need employeeid and contactid as the key. Otherwise you would need contactcompany and employeecompany?

Just some ideas based on how I read your request. A course or good book on advanced Entity design would really help ensure you get the best possible design from the start and can save you lots of time in the long run trying to discover and refactor as you go.

Upvotes: 1

Related Questions