sql – Many-to-many relationship with constraints

Question:

There are the following tables in the database

CREATE TABLE dbo.Items (
  Id INT IDENTITY,
  OperationId INT NOT NULL,
  CONSTRAINT PK_Items_Id PRIMARY KEY CLUSTERED (Id)
) ON [PRIMARY]
GO

ALTER TABLE dbo.Items
ADD CONSTRAINT FK_Items_Operations_Id FOREIGN KEY (OperationId) REFERENCES dbo.Operations (Id)
GO

CREATE TABLE dbo.Assemblies (
  Id INT IDENTITY,
  ParentId INT NULL,
  CONSTRAINT PK_Assemblies_Id PRIMARY KEY CLUSTERED (Id)
) ON [PRIMARY]
GO

ALTER TABLE dbo.Assemblies
ADD CONSTRAINT FK_Assemblies_Assemblies_Id FOREIGN KEY (ParentId) REFERENCES dbo.Assemblies (Id)
GO

I need to keep the relationship between Item & Assembly , when adding to the relationship, I need to fill in several fields (list of fields and their types in the table below)

CREATE TABLE dbo.tempTable (
  Position VARCHAR(50) NOT NULL,
  Overhang DECIMAL NOT NULL,
  Runtime DECIMAL NOT NULL,
  AmountCuttingEdge INT NULL,
  AmountPlates INT NULL,
  Durability DECIMAL NOT NULL,
  Note VARCHAR(50) NULL
) ON [PRIMARY]
GO

Originally I intended to add tempTable field ItemId , AssemblyId and make key component of these fields plus field Position that would ensure uniqueness, but as it turns out this is not enough: you must also be able to specify the assembly with the specified parameters ( Position , Overhang , … ) for multiple items.

If the assembly parameters were stored in the Assemblies table, then I would create an intermediate table with the ItemId & AssemblyId fields. but for the same assembly, these parameters may change (it depends on the item). Therefore, I want to move them (parameters) into a separate table, but in this case I don’t know how to create a connection between these data?

Tell me how to make a many-to-many relationship in this described case.

Answer:

I suggest two tables:

  • Link Characteristics Table (AI_link_characteristics)

    • id
    • Position
    • Durability
    • etc
  • Link table (AI_links):

    • assembly_id -> Assemblies.id
    • item_id -> Items.id
    • ai_link_id -> AI_link_characteristics.id
    • Primary key (assembly_id, item_id) // Or a separate auto-increment id
Scroll to Top