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
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
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 (
Overhang , … ) for multiple items.
If the assembly parameters were stored in the
Assemblies table, then I would create an intermediate table with the
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.
I suggest two tables:
Link Characteristics Table (AI_link_characteristics)
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