Database design question

[Ixiterra]

Although my question is relatively database-independent, I'm using PostgreSQL.

I'm new to designing databases, and understanding some of the design concepts while maintaining speed and flexibility has proven to be a big task, but I'm definitely trying. I've come across something that has kinda stumped me as the best route to take, though.

It's a parts database, so the main table is a list of parts. This table has only 4 columns. The part number, the cost ID (fk), the series ID (fk), and a unique primary key ID because there can be different parts with the same part number (different manufacturers and so on). This will be by far the largest table in terms of rows, but it will be a reasonable size of something no more than 50-100k rows.

The series table contains information shared by any part number in the same group of parts. Just as an example, a GE 60W lightbulb and a GE 75W lightbulb would have the same series ID, "GE incandescent lightbulbs". The series table contains information that applies to all GE lightbulbs, such as the manufacturer ID (GE), the category type (lightbulbs), and so on.

The cost table shares cost information (there is more than just the base price) among similar but different part numbers in the same series. So a GE 60W daylight bulb and a GE 60W bright white bulb would share the same cost ID. This table does *not* contain the manufacturer ID though since it is possible to figure that out by finding a part number with this cost ID.

The problem I see arises when someone wants to filter cost groups by manufacturer (this will be relatively rare and only used when updating pricing maybe once or twice a year or so). Instead of being simply able to filter by WHERE cost.mfr_id = 'x', every single part number must be queried (and JOINed with series?) to find out what manufacturer belongs to what cost.

This makes RDMS sense, right? I'm sure there will be end-user filters down the road that will run into this kind of problem, and I want to make sure there is very little lag from the time they hit search until the time they see a result. Am I on the right track? I should definitely index cost and series IDs in the parts list to make this much faster, right? I should definitely *not* be making "lookup" tables with columns like mfr_id and cost_id only, right?

Thanks for your input.