how to design database with relationship

Abdalla Elawad

Hello Guys

Please Help Me

Firstly I know is this scenario for IT Inventory is right or wrong ? then I will design new system and database for IT Inventory, I designed 3 tables as below:

Employees TBL: EmpNo. , EmpID , EmpName , Telephone , Dept. , Mobile ,Email , City , Branch.

New Item TBL : ItemID , ItemBarcode , Model , ItemDesc. , PartNo.

Custody TBL: Connect item With Employee : EmpNo. , ItemBarcode , Model , ItemDesc. , PartNo.

I want to define the relationship between three tables above.

Thanks.

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo8y ago
Accepted answer
1. Employees TBL: EmpNo. , EmpID , EmpName , Telephone , Dept. , Mobile ,Email , City , Branch.

make EmpNo to primary which will be the forgein key in the table Custody TBL.

2 .Custody TBL: Connect item With Employee : EmpNo. , ItemBarcode , Model , ItemDesc. , PartNo.

here for primary key add a field like CustodyId , make EmpNo to forgein key which is primary key in Employee TBL and also take PartNo as forgein key which should be the primary key
in New Item TBL otherwise sir if you want to make ItemID as primary key in New Item TBL then sir you have to add a field in Custody TBL as ItemID and make it forgein key

3.New Item TBL : ItemID , ItemBarcode , Model , ItemDesc. , PartNo.

If sir you make PartNo forgein key in Costody TBL make here primary key to PartNo
otherwise if sir you make ItemID forgein key in Costody TBL make here primary key to ItemID
Laxmidhar Sahoo8y ago
Sir
that table isa transction table .So sir please define the that table with two foregein key
references to Employee TBL and New Item TBL those are primary key in thoes tables

Thanks
Abdalla Elawad8y ago
I am very grateful to all of you
Abdalla Elawad8y ago
Thank you all guys
really you are supported my problem

Please see to last additional inforamation related to Recipient Employee

Thanks
Abdalla Elawad8y ago
but i need to add information , There is an employee who receives an item instead of another employee. The employee who did not receive the item is registered because it is possible to be in another city

Name :
EmplyeeID
Telephone:
Signature:
Esakki Muthu8y ago
Hi Abdalla,
Copy paste the following code,
USE [MergeChecking]
GO
/****** Object:  Table [dbo].[tbl_Custody]    Script Date: 11/28/2017 7:54:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Custody](
    [CustodyId] [int] IDENTITY(1,1) NOT NULL,
    [EmpId] [int] NOT NULL,
    [ItemId] [int] NOT NULL,
CONSTRAINT [PK_tbl_Custody] PRIMARY KEY CLUSTERED
(
    [CustodyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tbl_Employee]    Script Date: 11/28/2017 7:54:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Employee](
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmpNumber] [varchar](50) NULL,
    [Name] [varchar](70) NULL,
    [ContactTele] [bigint] NULL,
    [Department] [varchar](50) NULL,
    [Mobile] [bigint] NULL,
    [Email] [nvarchar](254) NULL,
    [City] [varchar](50) NULL,
    [Brance] [varchar](50) NULL,
CONSTRAINT [PK_tbl_Employee] PRIMARY KEY CLUSTERED
(
    [EmpId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_Items]    Script Date: 11/28/2017 7:54:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Items](
    [ItemId] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](100) NULL,
    [Barcode] [nvarchar](50) NULL,
    [Model] [varchar](50) NULL,
    [PartNo] [varchar](50) NULL,
CONSTRAINT [PK_tbl_Items] PRIMARY KEY CLUSTERED
(
    [ItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Custody]  WITH CHECK ADD  CONSTRAINT [FK_tbl_Custody_tbl_Employee] FOREIGN KEY([EmpId])
REFERENCES [dbo].[tbl_Employee] ([EmpId])
GO
ALTER TABLE [dbo].[tbl_Custody] CHECK CONSTRAINT [FK_tbl_Custody_tbl_Employee]
GO
ALTER TABLE [dbo].[tbl_Custody]  WITH CHECK ADD  CONSTRAINT [FK_tbl_Custody_tbl_Items] FOREIGN KEY([ItemId])
REFERENCES [dbo].[tbl_Items] ([ItemId])
GO
ALTER TABLE [dbo].[tbl_Custody] CHECK CONSTRAINT [FK_tbl_Custody_tbl_Items]
GO
While selecting the record you can put join and get the records, Let me know if you have any questions.
Nilesh Patil8y ago
Hi,

Refer below tutorial , here is good explanation of relational databse design

https://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html
Rajkiran Swain8y ago
I think EmpNo and ItemID both are as a Primary Key in a table . You may follow https://www.techrepublic.com/article/relational-databases-defining-relationships-between-database-tables/