I have table Work. it's look like
我有桌子工作。它看起来像
CREATE TABLE work
(
work_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
type_of_service_conn int NOT NULL,
period_of_execution int NOT NULL,
mechanic_conn int NOT NULL,
spare_conn int NOT NULL,
FOREIGN KEY (mechanic_conn) REFERENCES mechanic(mechanic_id),
FOREIGN KEY (type_of_service_conn) REFERENCES type_of_service(type_of_service_id),
FOREIGN KEY (spare_conn) REFERENCES spares(spare_id),
)
And i have table Spares
我有桌子备件
CREATE TABLE spares
(
spare_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
name_of_spare nvarchar NOT NULL,
price_for_spare decimal NOT NULL
)
For example, for repair of the engine are needed bolts, washers and nuts. How to link these tables?
例如,为了修理发动机,需要螺栓,垫圈和螺母。如何链接这些表?
1 个解决方案
#1
What you are looking for are probably 1:n relationships.
您正在寻找的可能是1:n关系。
You need a third table, that stores what kind of parts are used for what repair_id, e.g.
你需要一个第三个表,它存储什么类型的部件用于什么repair_id,例如
CREATE TABLE spares_for_work (
work_id INT NOT NULL,
spare_id INT NOT NULL,
count INT NOT NULL,
FOREIGN KEY (work_id) REFERENCES work(work_id),
FOREIGN KEY (spare_id) REFERENCES spares(spare_id) );
Then you cant put multiple parts in a work_id. E.g.: you put in spares_for_work
:
然后你不能把多个部分放在work_id中。例如:你输入了spares_for_work:
work_id = 1, spare_id=12, count=1
work_id = 1, spare_id=16, count=12
...
When you then select from spares_for_work
the work_id
1
, you will get all spares needed for that work.
然后,当您从spares_for_work中选择work_id 1时,您将获得该工作所需的所有备件。
#1
What you are looking for are probably 1:n relationships.
您正在寻找的可能是1:n关系。
You need a third table, that stores what kind of parts are used for what repair_id, e.g.
你需要一个第三个表,它存储什么类型的部件用于什么repair_id,例如
CREATE TABLE spares_for_work (
work_id INT NOT NULL,
spare_id INT NOT NULL,
count INT NOT NULL,
FOREIGN KEY (work_id) REFERENCES work(work_id),
FOREIGN KEY (spare_id) REFERENCES spares(spare_id) );
Then you cant put multiple parts in a work_id. E.g.: you put in spares_for_work
:
然后你不能把多个部分放在work_id中。例如:你输入了spares_for_work:
work_id = 1, spare_id=12, count=1
work_id = 1, spare_id=16, count=12
...
When you then select from spares_for_work
the work_id
1
, you will get all spares needed for that work.
然后,当您从spares_for_work中选择work_id 1时,您将获得该工作所需的所有备件。