Database Design Question (How to handle alternate part numbers?)

[clarkj]

How should a database be designed to give alternate part numbers? I have looked at a single table with MainPartNum and AltParNum. But this seems very redundant and would be very difficult to manage. An alternate design I have thought about is a table with a large number of AltPartNum fields that may not always get used. Again this just seems wrong to me. Below is some example data could you please explain to me how best to manage this?

Part Number AAA-AAAA
Alternates BBB-BBBB, CCC-CCCC

Part Number BBB-BBBB
Alternates AAA-AAAA, CCC-CCCC

Part Number CCC-CCCC
Alternates AAA-AAAA, BBB-BBBB

Part Number DDD-DDDD
Alternates None

Part Number EEE-EEEE
Alternates FFF-FFFF

Part Number FFF-FFFF
Alternates EEE-EEEE

Thank You

[rmiao]

Create another table for alternate part numbers and set relation to main part table.