How to design Database& define Relationship between tables

Abdalla Elawad

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

8 Replies

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

Esakki Muthu8y ago
Accepted 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(1,1) 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(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_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(1,1) 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 Muthu8y ago
When the posted answer is solved your problem , please accept that post as your answer
Abdalla Elawad8y ago
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 Muthu8y ago
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 Elawad8y ago
Thank you Essakki

It is successfully working now , but are you agree with me ,are there relationships between tables that are correct?
Abdalla Elawad8y ago
Hi
Esakki Muthu

please see the error
Abdalla Elawad8y ago
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 Muthu8y ago
Hi Bro,
Kindly use the following code to create the tables with relationships between the tables.
CREATE TABLE [dbo].[tbl_Employee](
    [EmpId] [int] IDENTITY(1,1) 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(1,1) 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(1,1) 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.