Database table designing for sql server-CodePudding

I have a two table named customer and Group_Customer. Customer table has NID, Name and Father_Name field where NID is primary key. And Group_Customer has GroupID, Group_Name, and NID field where NID is foregin key of customer table. I want to add a group customer which have 3 records of customer table. When i add Group Customer with the primary key of Customer NID field it could not be one record. Is it possible one record of group customer with two record of customer?

tblCustomer

NID     Name                Father_Name
101555      Mr. Debashish       Mr. Deb kumar
101556      Mr. Nibash      Mr. Debashish
101557      Nandika Rani        Mr. Debashish

tblGroup_Customer

GroupID     Group_Name          NID
1001        Mr. Debashish and Brothers  101555
1001        Mr. Debashish and Brothers  101556

The group customer name 'Mr. Debashish and Brothers' which has two customers and there NID is 101555 and 101556. Is it possible one record of group customer with two record of customer?

CodePudding user response：

Yes it's possible to have multiple customers of one group.

Reason is that you can have multiple foreign keys in a table, while making sure that the foreign key column should not be set to unique.

CodePudding user response：

You should read up on a technique called normalization which will make these things more clear to you.

If what you need is that a group can have, 1 or more customers, than you should add a column GroupID to your customer table

create table tblGroup (
  GroupID int not null, 
  GroupName varchar(100),
  constraint PK_GroupID primary key (GroupID)
)
  
create table tblCustomer (
  CustomerID int not null, 
  Name varchar(100), 
  FatherName varchar(100),
  GroupID int not null,
  constraint PK_CustomerID primary key (CustomerID),
  constraint FK_Customer_Group foreign key (GroupID) references tblGroup (GroupID)
  )

Consider this example where you can see how to create, populate and query the tables

In this example, every customer must have a group, if that is not required then create the column GroupIDin tblCustomer as nullable, GroupID int null
In this case a customer can exist without being a member of a group