Think about this example table (presuming SQL Server 2005):
create table product_bill_of_materials ( parent_product_id int not null, child_product_id int not null, quantity int not null )
I am thinking about an amalgamated primary key that contains the 2 product_id posts (I'll certainly desire a unique constraint) instead of another unique ID column. Real question is, from the performance perspective, should that primary key be clustered?
Must I also create a catalog on each ID column to ensure that searches for that foreign secrets are faster? In my opinion this table will get hit a lot more on reads than creates.
As was already stated by a number of others, it is dependent how you'll access the table. Bear in mind though, that any RDBMS available should have the ability to make use of the clustered index for searching with a single column as lengthy as that column seems first. For instance, in case your clustered index is on (parent_id, child_id) you do not need another separate index on (parent_id).
Your best choice might be a clustered index on (parent_id, child_id), that also is actually the main key, having a separate non-clustered index on (child_id).
Ultimately, indexing ought to be addressed after you have got a concept of the way the database is going to be utilized. Develop some standard performance stress tests if you're able to after which evaluate the behaviour utilizing a profiling tool (SQL Profiler for SQL Server) and gratifaction tune after that. Without having the expertise or understanding to achieve that in advance, then apply for a (hopefully limited) discharge of the applying, collect the performance metrics, and find out where you have to improve performance and evaluate which indexes can help.
Should you choose things right, you need to have the ability to capture the "typical" profile of methods the database is utilized and you will then rerun that again and again again on the test server while you try various indexing approaches.
Inside your situation I'd most likely just put a clustered PK on (parent_id, child_id) to begin with after which add the non-clustered index only when I saw a performance problem that might be assisted because of it.