Help verifying correct index/key implementation.
Posted on 2005-05-04
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.
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?