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(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 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/

how to design database with relationship

elham deljooei

Nilesh Avhad