1

I have a customers tables, the customer can be either a company or individual. If the customer is a company, then it has specific structure, that means that each company has a (Director , secretary, shareholder) = roles. Any customer (individual or company) can play one or more roles within the structure of the company.

As an example, in the customers table I have:

  • Bill Gates as an individual
  • Tim Cook as an individual
  • Microsoft as a company
  • Apple as a company

Company Microsoft

  • Bill Gates as director and as shareholder
  • Tim Cook as secretary and shareholder

Company Apple

  • Bill Gates as director and as shareholder
  • Tim Cook as Director and secretary
  • Microsoft as a shareholder

So I ended creating a CompanyStructure table which has two foreign keys relationships with the customers table:

  • Customer.ID --> CompanyStructure.ParentID
  • Customer.ID --> CompanyStructure.ChildID

I couldn't create the relationship using the system, so I am updating these tables programmatically by entering in the CompanyStructure table, The Customers.Id twice one as parent and one as child plus the roles.id.

My question is:
The current relationship between Customers and CompanyStructure is many to many, so it is a bad design, right? Is there a way to achieve the goal I want but in another way? A better way?

The CompanyStructure Table is the junction table between the Customers and Roles tables, but the problem is that each record in the CompanyStructure table has two FK as Customer.ID from the Customers table, one as Parent and one as Child, as per the above example, the record that describes the company Microsoft as: Customer.ID --> CompanyStructure.ParentIDCustomer.ID = Microsoft , as parent , Customer.ID --> CompanyStructure.ChildID = Bill gates as Director. Same goes for Gates as shareholder also another two records for Tom Cook.

CREATE TABLE [dbo].[Customers](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [nvarchar](64) NOT NULL,
    [TypeID] [int] NOT NULL,
    [IDCard] [nvarchar](64) NULL,
    [PassportID] [nvarchar](64) NULL,
    [L1Name] [nvarchar](64) NOT NULL,

 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
    [ID] 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].[Customers]  WITH CHECK ADD  CONSTRAINT [FK_Customers_CustomerTypes] FOREIGN KEY([TypeID])
REFERENCES [dbo].[CustomerTypes] ([ID])
ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [FK_Customers_CustomerTypes]
GO

ALTER TABLE [dbo].[Customers] ADD  CONSTRAINT [DF__Customer__TypeID__01142BA1]  DEFAULT ((1)) FOR [TypeID]
GO

CREATE TABLE [dbo].[CustomerTypes](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerTypeID] [nvarchar](64) NOT NULL,
    [L1Name] [nvarchar](64) NOT NULL,

 CONSTRAINT [MRTYP_PK] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[CustomerRoles](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerRolesID] [nvarchar](64) NOT NULL,
    [Status] [bit] NOT NULL,
    [L1Name] [nvarchar](64) NOT NULL,

 CONSTRAINT [MRNMS_PK] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[CompanyStructure](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerParentID] [int] NOT NULL,
    [CustomerChildID] [int] NOT NULL,
    [CustomerRole] [int] NOT NULL,
    [StartDate] [date] NOT NULL,
    [EndDate] [date] NULL,

 CONSTRAINT [PK_CompanyStructure] PRIMARY KEY CLUSTERED 
(
    [CustomerParentID] ASC,
    [CustomerChildID] ASC,
    [CustomerRole] ASC,
    [StartDate] 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].[CompanyStructure]  WITH CHECK ADD  CONSTRAINT [FK_CompanyStructure_CustomerRoles] FOREIGN KEY([CustomerRole])
REFERENCES [dbo].[CustomerRoles] ([ID])
ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[CompanyStructure] CHECK CONSTRAINT [FK_CompanyStructure_CustomerRoles]
GO

ALTER TABLE [dbo].[CompanyStructure]  WITH CHECK ADD  CONSTRAINT [FK_CompanyStructure_Customers] FOREIGN KEY([CustomerParentID])
REFERENCES [dbo].[Customers] ([ID])
GO

ALTER TABLE [dbo].[CompanyStructure] CHECK CONSTRAINT [FK_CompanyStructure_Customers]
GO

ALTER TABLE [dbo].[CompanyStructure]  WITH CHECK ADD  CONSTRAINT [FK_CompanyStructure_Customers1] FOREIGN KEY([CustomerChildID])
REFERENCES [dbo].[Customers] ([ID])
GO

ALTER TABLE [dbo].[CompanyStructure] CHECK CONSTRAINT [FK_CompanyStructure_Customers1]
GO
  • 2
    Are you looking for a junction table? – Max Vernon Mar 4 '16 at 0:43
  • 1
    Why do all tables have both an ID and a TableID column? – ypercubeᵀᴹ Mar 6 '16 at 11:24
  • @ypercubeᵀᴹ , no specific reason, but I found it easier to build the relationship using table ID, my question is, is there away to get my goal without having many to many relationship between the tables (customers, CompanyStructure)? if you were requested to do this scenario with (Bill, Tom, Microsoft, Apple), how would you design it? – user88767 Mar 6 '16 at 22:29
  • A table/relation represents/records an application relationship/association. There is nothing wrong with a many:many table. It will have foreign keys to participant entities. A foreign key says that referencing columns' values must appear as referenced columns' values. However many methods/products call foreign keys relationships. Such a relationship is by its nature many:1. You can't have a many:many one. – philipxy Mar 7 '16 at 19:17
  • @philipxy , can you please explain little bit more, I didn't really understand your point, I am kind of new to database design, thanks in advance. – user88767 Mar 7 '16 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.