CODEWITHSUNDEEP
sqlsql-serverdatabaserelationship
wambo
wambo

Reputation: 77

MSSQL : One to Many Relation to own Table

I got the PrimaryKey "InventoryID" and the the Field "RelatedTo" which is a Reference to the primary Key Im trying to create the Table Like this:

InventoryID: (1) is RelatedTo InventoryID; (2) & (3)

For Example: Laptop (1) has Relation to Power Supply(2) and a Relation to Monitor(3) But Power Supply(2) & Monitor(3) have also a relation to Laptop (1).

The RelationTo Field can also be null (optional)

On Update it should change on both ways. Update: Monitor (3) RelatedTo Laptop(4) <-> Laptop(1) RelatedTo Power Supply (2)

On Delete it shouldnt delete all relatedTo Devices !onCascade.

this is my current database enter image description here

Create Table DeviceType
(
DeviceID int Identity (1,1) primary key,
DeviceName nvarchar(255) not null
);


Create Table Users
(
UserID int Identity (1,1) primary key,
FirstName nvarchar(100),
LastName nvarchar(100),
Email nvarchar(255)
);

create table InventoryData(
InventoryNo int primary key not null,
Device_ID int not null, 
Manufacturer nvarchar(255) not null,
Model nvarchar(255) not null,
SerialNo nvarchar(255), 
DGUVV3 bit, 
PowerSupply nvarchar(255),
CurrentConsumption nvarchar(255),
RelatedTo int,
CreatorID int not null, 
OwnerID int not null, 
CreatedTimeStamp Datetime2,
ModifiedTimeStamp Datetime2,

Foreign Key (CreatorID) references Users(UserID),
Foreign Key (OwnerID) references Users(UserID),
Foreign Key (Device_ID) references DeviceType(DeviceID),
Foreign Key (RelatedTo) references InventoryData(InventoryNo)
);

I hopefully you gonna understand what im trying to implement to my database :)

Upvotes: 0

Views: 49

Answers (1)

H&#233;ctor Asencio L
H&#233;ctor Asencio L

Reputation: 141

For the sake of relational databases (1NF), you should use a second table with two FKs (first one would be InventoryNo and second one would be related, defined as primary key), each one as a separate FK.

If you don't want to use a second table, you can store a JSON object inside a single field and deal with that JSON object later. And not use physical delete on tables.

Upvotes: 1

Related Questions