how to design database with relationship

Question

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.

Laxmidhar Sahoo · 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

Abdalla Elawad · Answer

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:

Laxmidhar Sahoo · Answer

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 Elawad · Answer

I am very grateful to all of you

Abdalla Elawad · Answer

Thank you all guys really you are supported my problem Please see to last additional inforamation related to Recipient Employee Thanks

Esakki Muthu · Answer

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(11) 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(11) 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(11) 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 Patil · Answer

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 Swain · Answer

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/