Search code examples
sqldatabase-designmany-to-many

Many-to-many relations in database design

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 modeling this relation with translation tables:

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.

What are better ways of doing this, like somehow storing and updating lists in single columns under their respective tables?

I'm trying minimize query time and post-processing intensity.

Solution

  • This is not naive, this is the proper way of an ER model. Separating entities with relations, the classic design pattern. Worry not about query/join overhead, RDBMSs are optimized for this and can fly through those join queries.

    You can also make the relation table have (part_id,machine_id) as a compound primary key. Better yet, create them as indexed organized tables and avoid any (negligible) overhead of the table data.