Many-to-many relations in database design

Question

I'm building a database that makes use of lots of many-to-many relations. For example, I have part items and machine items where each part goes into many machines and each machine has many parts. The purpose of this database is to be able to input a list of parts and get back a list of machines composed of only parts in that list, or vice versa. I'm new to database design, so I've currently been modeling this relation with translation tables, like such:

create table machine ( machine_id number, machine_name char(30) )

create table part ( part_id number, part_name char(30) )

create table relations ( part_id number, machine_id number )

This seems like a very ugly and naive way to go about modeling this sort of relation. Are there better ways of doing this, like somehow storing and updating lists in single columns under their respective tables or something better I haven't thought of yet? I'm trying to find a way to do this that minimizes query time and post-processing intensity.

Thanks.

Answer 1

Welcome to relational databases. No, there isn't a better way. I'd recommend naming your tables slightly better; Machine, Part, Machine_Part or something of the sort, because you're probably going to end up with a bunch of these type of tables.

Post processing isn't really an issue, it's fairly easy to deal with these relationships using simple INNER JOINs in SQL or using an ORM. Databases are designed to cope with this kind of stuff.