How to design Database& define Relationship between tables

Question

Hi Guys Please Help Me Yesterday Uploaded my Problem but now added new information as new analysis for IT Inventory  I want to help me please how build strucure of database correct and define the relationship between tables : attached photos as full details for System manul. and this is model deals with manul : Thanks

Esakki Muthu · Answer

Hi I have created those tables in my machine from there I just created the script copy this code and execute and try again. If required do the changes on the below query. USE [MergeChecking] GO /****** Object: Table [dbo].[tbl_Custody] Script Date: 11/29/2017 10:01: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 [PartNo] [ 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/29/2017 10:01: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_EmployeeReceipt] Script Date: 11/29/2017 10:01:12 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_EmployeeReceipt]( [ReceiptId] [ int ] IDENTITY(11) NOT NULL [EmpName] [varchar](70) NULL [EmpId] [ int ] NULL [Mobile] [bigint] NULL CONSTRAINT [PK_tbl_EmployeeReceipt] PRIMARY KEY CLUSTERED ( [ReceiptId] 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/29/2017 10:01:12 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_Items]( [PartNo] [ int ] NOT NULL [ItemId] [ int ] NULL [Description] [nvarchar](100) NULL [Barcode] [nvarchar](50) NULL [Model] [varchar](50) NULL CONSTRAINT [PK_tbl_Items] PRIMARY KEY CLUSTERED ( [PartNo] 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([PartNo]) REFERENCES [dbo].[tbl_Items] ([PartNo]) GO ALTER TABLE [dbo].[tbl_Custody] CHECK CONSTRAINT [FK_tbl_Custody_tbl_Items] GO ALTER TABLE [dbo].[tbl_EmployeeReceipt] WITH CHECK ADD CONSTRAINT [FK_tbl_EmployeeReceipt_tbl_Employee] FOREIGN KEY([EmpId]) REFERENCES [dbo].[tbl_Employee] ([EmpId]) GO ALTER TABLE [dbo].[tbl_EmployeeReceipt] CHECK CONSTRAINT [FK_tbl_EmployeeReceipt_tbl_Employee] GO

Esakki Muthu · Answer

When the posted answer is solved your problem  please accept that post as your answer

Abdalla Elawad · Answer

Thank you Essaki for more Explanation for your this comment Not sure why you need empname and mobile in receipt table  because we hhave empid in that table right using that we can get all required columns. for exmple i can recieved the specific item to another person in another city  it will connected this item with me until he is recive it  transfer to that person when he is signature .

Esakki Muthu · Answer

1) tbl_Employee is the main table to store employee informations (EmpId Is the PK column) 2) tbl_Items is the main table to store the items details (PortNo is the PK column) 3) tbl_custody is the transaction table for employee and items( here custody Id is the the PK column for this table and and EmpId is the FK column with tbl_Employee table and Portno is the FK column with tbl_Items table) 4) tbl_EmployeeReceipt ( Here empid is the FK column ) Not sure why you need empname and mobile in receipt table  because we hhave empid in that table right using that we can get all required columns.

Abdalla Elawad · Answer

Thank you Essakki It is successfully working now  but are you agree with me are there relationships between tables that are correct?

Abdalla Elawad · Answer

Hi Esakki Muthu please see the error

Abdalla Elawad · Answer

Please see the Error firstly as attached photo & you are not the information about the data of other employee who is recipit custodty instead of other employee Recipit Employee tbl : EmpName EmpID Mobile

Esakki Muthu · Answer

Hi Bro Kindly use the following code to create the tables with relationships between the tables. CREATE TABLE [dbo].[tbl_Employee]( [EmpId] [ int ] IDENTITY(11) NOT NULL [EmpNumber] [varchar](50) NULL [EmpName] [varchar](70) NULL [Barcode] char (13) 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 CREATE TABLE [dbo].[tbl_Items]( [ItemId] [ int ] IDENTITY(11) NOT NULL [Description] [nvarchar](100) NULL [Barcode] [nvarchar](50) NULL [Model] [varchar](50) NULL [PartNo] int NULL [ReceiveDate] Datetime 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 CREATE TABLE [dbo].[tbl_Custody]( [CustodyId] [ int ] IDENTITY(11) NOT NULL [EmpId] [ int ] NOT NULL [PartNo] [ int ] NOT NULL [ReceiveDate] Datetime 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 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([PartNo]) REFERENCES [dbo].[tbl_Items] ([PartNo]) GO ALTER TABLE [dbo].[tbl_Custody] CHECK CONSTRAINT [FK_tbl_Custody_tbl_Items] Let me know if you have any questions.