0
\$\begingroup\$

I'm posting a follow-up question based on my first question. My goal is to, as an exercise for myself, create a complex (in my opinion) e-commerce application. The explanation of the application can be found in my first question.

This concerns the parties of my application. It can have Users & Companies. Companies always have an owner (user) and they can have Employees who act on behalf of the company. Companies can buy products from other companies, users can buy products from companies. To negotiate the internal messaging system will be used.

  1. How can I make sure I know which of the company contacts (employees) was in contact (messaging) with the buyer (who also can be an employee of another company)? Maybe this should also include orders as I would also like to know which employee made the purchase or which employee sold the product.

  2. How do I make sure an employee is linked to 1 company? And that employee cannot act in the role of user.

This is my db schema: (updated with missing relationship between user and company)

DB Schema

As requested I've only put a subset of code to be reviewed. This is the part with Parties (Users and Companies) and Messages.

This is the SQL code:

-- ************************************** [dbo].[PartyType]

CREATE TABLE [dbo].[PartyType]
(
 [PartyTypeCode] nvarchar(5) NOT NULL ,
 [Description]   nvarchar(50) NOT NULL ,
 [Name]          nvarchar(50) NOT NULL ,


 CONSTRAINT [PK_PartyType] PRIMARY KEY CLUSTERED ([PartyTypeCode] ASC)
);
GO

-- ************************************** [dbo].[Party]

CREATE TABLE [dbo].[Party]
(
 [PartyId]       uniqueidentifier NOT NULL ,
 [PartyTypeCode] nvarchar(5) NOT NULL ,


 CONSTRAINT [PK_Party] PRIMARY KEY CLUSTERED ([PartyId] ASC),
 CONSTRAINT [PartyToPartyType_FK] FOREIGN KEY ([PartyTypeCode])  REFERENCES [dbo].[PartyType]([PartyTypeCode])
);
GO

-- ************************************** [dbo].[User]

CREATE TABLE [dbo].[User]
(
 [UserId] uniqueidentifier NOT NULL ,


 CONSTRAINT [PK_User] PRIMARY KEY NONCLUSTERED ([UserId] ASC),
 CONSTRAINT [FK_18] FOREIGN KEY ([UserId])  REFERENCES [dbo].[Party]([PartyId])
);
GO


-- ************************************** [dbo].[Company]

CREATE TABLE [dbo].[Company]
(
 [CompanyId] uniqueidentifier NOT NULL ,
 [OwnerId]   uniqueidentifier NOT NULL ,


 CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED ([CompanyId] ASC),
 CONSTRAINT [FK_21] FOREIGN KEY ([CompanyId])  REFERENCES [dbo].[Party]([PartyId]),
 CONSTRAINT [FK_243] FOREIGN KEY ([OwnerId])  REFERENCES [dbo].[User]([UserId])
);
GO

-- ************************************** [dbo].[Contact]

CREATE TABLE [dbo].[Contact]
(
 [ContactId] uniqueidentifier NOT NULL ,
 [CompanyId] uniqueidentifier NOT NULL ,


 CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED ([ContactId] ASC),
 CONSTRAINT [FK_229] FOREIGN KEY ([CompanyId])  REFERENCES [dbo].[Company]([CompanyId])
);
GO

-- ************************************** [dbo].[Thread]

CREATE TABLE [dbo].[Thread]
(
 [ThreadId] uniqueidentifier NOT NULL ,


 CONSTRAINT [PK_Thread] PRIMARY KEY CLUSTERED ([ThreadId] ASC)
);
GO

-- ************************************** [dbo].[ThreadParticipator]

CREATE TABLE [dbo].[ThreadParticipator]
(
 [ThreadId] uniqueidentifier NOT NULL ,
 [PartyId]  uniqueidentifier NOT NULL ,


 CONSTRAINT [PK_ThreadParticipator] PRIMARY KEY CLUSTERED ([PartyId] ASC, [ThreadId] ASC),
 CONSTRAINT [FK_100] FOREIGN KEY ([PartyId])  REFERENCES [dbo].[Party]([PartyId]),
 CONSTRAINT [FK_97] FOREIGN KEY ([ThreadId])  REFERENCES [dbo].[Thread]([ThreadId])
);
GO

-- ************************************** [dbo].[Message]

CREATE TABLE [dbo].[Message]
(
 [MessageId] uniqueidentifier NOT NULL ,
 [ThreadId]  uniqueidentifier NOT NULL ,
 [AuthorId]  uniqueidentifier NOT NULL ,


 CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED ([MessageId] ASC),
 CONSTRAINT [FK_211] FOREIGN KEY ([ThreadId])  REFERENCES [dbo].[Thread]([ThreadId]),
 CONSTRAINT [FK_214] FOREIGN KEY ([AuthorId])  REFERENCES [dbo].[Party]([PartyId])
);
GO

-- ************************************** [dbo].[MessageReadState]

CREATE TABLE [dbo].[MessageReadState]
(
 [MessageId] uniqueidentifier NOT NULL ,
 [PartyId]   uniqueidentifier NOT NULL ,


 CONSTRAINT [PK_MessageReadState] PRIMARY KEY CLUSTERED ([MessageId] ASC, [PartyId] ASC),
 CONSTRAINT [FK_88] FOREIGN KEY ([MessageId])  REFERENCES [dbo].[Message]([MessageId]),
 CONSTRAINT [FK_91] FOREIGN KEY ([PartyId])  REFERENCES [dbo].[Party]([PartyId])
);
GO

-- ************************************** [dbo].[Address]

CREATE TABLE [dbo].[Address]
(
 [AddressId] uniqueidentifier NOT NULL ,


 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED ([AddressId] ASC)
);
GO

-- ************************************** [dbo].[PartyAddress]

CREATE TABLE [dbo].[PartyAddress]
(
 [PartyId]   uniqueidentifier NOT NULL ,
 [AddressId] uniqueidentifier NOT NULL ,


 CONSTRAINT [PK_PartyAddress] PRIMARY KEY CLUSTERED ([AddressId] ASC, [PartyId] ASC),
 CONSTRAINT [FK_55] FOREIGN KEY ([PartyId])  REFERENCES [dbo].[Party]([PartyId]),
 CONSTRAINT [FK_58] FOREIGN KEY ([AddressId])  REFERENCES [dbo].[Address]([AddressId])
);
GO
\$\endgroup\$
  • \$\begingroup\$ Can you add some more detail about your entities? What defines a party, party type and contact? \$\endgroup\$ – Wes H Jun 7 at 14:10
  • \$\begingroup\$ @Wes, trying to answer this question got me thinking that I might not need the party and contact entities. If I were to replace Party with User And have the relationship that a company can have 1 to many users and that a user belongs to 0 or 1 company and maintain the partytype entity to know what type of user it is, then it would be more logical and more realistic I think. A User would always be needed then to perform some sort of interaction and based on the FK it can be determined if the user is linked to a company or not. \$\endgroup\$ – JeremyVm Jun 8 at 8:17

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.