Help verifying correct index/key implementation.

I am in the process of building a SQL database which will contain standard rolodex information.  I would like to understand if I am implementing my indices and keys correctly.

I have a Company table with a primary key on the company_id(int) column.  I have a Division table which has a  division_id(int) column and a foreign key relation to the company table on the company_id column. I created a clusterd primary key on both the company_id and division_id columns.

Company
      Company_id <PK>
      Company_name
      ...
Divisions
      Division_ID<PK>
      Company_id<PK>
      Division_name
      ...

Say I have a company with a company_id of 100 and that company has five divisions (1-5).  In the divisions table, the data would be arranged like:

Division_id            Company_id            Division_Name
1                  100                  A
2                  100                  B
3                  100                  C
4                  100                  D
5                  100                  E


The company_id and division_id together identify a unique record.  These should never be a duplicate company_id/division_id combination in either of this table.  

Is it best to create clustered primary keys on the company_id and the division_id for the division  table?  Should I also contain the division name, as that will be a frequently searched column and there should not be any duplicate division names for a given company?
LVL 1
abenageAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
>>? Is it best to create clustered primary keys on the company_id and the division_id for the division table? << 

You should definitely create a unique index on those columns.  Whether or not it should be clustered is a different, and more complex, question.

In general, an index should be clustered if:

*) you often select a range of those values; for example, companies 1 thru 5
*) you often sort on those values; for example, ORDER BY company_id, division_id is used a lot
*) or group on those values; for example, GROUP BY company_id [, division_id] is used fairly frequently


>> Should I also contain the division name, as that will be a frequently searched column <<

It's almost certainly best to place it in it's own index, especially if you do make the (company_id, division_id) index clustered (because the clustered index keys will always be included in every non-clustered index, so the division name index would then automatically contain the company_id and division_id).
0
 
rafranciscoCommented:
>> Is it best to create clustered primary keys on the company_id and the division_id for the division  table? <<

A clustered primary key on the company_id and division_id is a good choice to avoid duplicates on the company_id/division_id and to speed up the join to the company table.

>> Should I also contain the division name <<

Since your index is already clustered, there is no need to include the division name.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.