SQL e-commerce database design

Question

I want as an exercise to create an e-commerce application from bottom up. My main goal here is to gain a lot of knowledge. I have experience in web development, but never have been there from the start of the project. The database schema was always already in place and also the server infrastructure (Azure) had been setup and configured. Those 2 things are my main goals for doing this exercise.

I've chosen to create quite a complex application (as far as I think) to make sure I encounter lots of questions so that I can learn a lot from this exercise.

My application would be some sort of e-commerce app where both users and companies can register. Companies have the possibility to add products. Products belong to a category and have attributes, discounts, quantities with specific prices. Users can then buy these products from the companies, but the companies can also buy from other companies.

Companies and Users (or company to company) would interact through a private messaging system (1 to 1) where users can request a quote and companies can answer with an offer. Companies also have the possibility to add employees who have the possibility to interact on behalf of the company. So every employee of the company should have access to all messages of the company.

When the party (user or company) buys a product(s) an order is created containing the specific details of the product(s) at that time. After payment the order is then shipped to the provided address of the party.

I would also like to get as many statistics as possible from the interactions occurring on the site. For example: total orders per company, the reaction time of a company to a message, the number of orders handled by employee x of company y...

I have tried to create my own db schema, but there are a lot of flaws in it I think. I have used existing questions to get to the schema I have now. I hope someone can help me in further designing my schema by giving me tips and hints or examples.

This is my 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


CREATE NONCLUSTERED INDEX [fkIdx_30] ON [dbo].[Party] 
 (
  [PartyTypeCode] ASC
 )

GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'classifies', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Party', @level2type=N'CONSTRAINT', @level2name=N'PartyToPartyType_FK';
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


CREATE NONCLUSTERED INDEX [fkIdx_18] ON [dbo].[User] 
 (
  [UserId] ASC
 )

GO

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

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


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


CREATE NONCLUSTERED INDEX [FK] ON [dbo].[Company] 

GO

CREATE NONCLUSTERED INDEX [fkIdx_21] ON [dbo].[Company] 
 (
  [CompanyId] ASC
 )

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


CREATE NONCLUSTERED INDEX [fkIdx_229] ON [dbo].[Contact] 
 (
  [CompanyId] ASC
 )

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


CREATE NONCLUSTERED INDEX [fkIdx_100] ON [dbo].[ThreadParticipator] 
 (
  [PartyId] ASC
 )

GO

CREATE NONCLUSTERED INDEX [fkIdx_97] ON [dbo].[ThreadParticipator] 
 (
  [ThreadId] ASC
 )

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


CREATE NONCLUSTERED INDEX [fkIdx_211] ON [dbo].[Message] 
 (
  [ThreadId] ASC
 )

GO

CREATE NONCLUSTERED INDEX [fkIdx_214] ON [dbo].[Message] 
 (
  [AuthorId] ASC
 )

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


CREATE NONCLUSTERED INDEX [fkIdx_88] ON [dbo].[MessageReadState] 
 (
  [MessageId] ASC
 )

GO

CREATE NONCLUSTERED INDEX [fkIdx_91] ON [dbo].[MessageReadState] 
 (
  [PartyId] ASC
 )

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


CREATE NONCLUSTERED INDEX [fkIdx_55] ON [dbo].[PartyAddress] 
 (
  [PartyId] ASC
 )

GO

CREATE NONCLUSTERED INDEX [fkIdx_58] ON [dbo].[PartyAddress] 
 (
  [AddressId] ASC
 )

GO

Answer 1

You really need to focus on the various "who" s in your model.

• Company
• Contact
• User
• Buyer
• Seller
• Party
• Participator

Try to separate the who from the role. The role is the relationship between the who and the event.

As a modelling example:

• a user is the entity that performs some action.
• a user must belong to one organization (or many, see broker below)
• An organization contains 0 to many users and can be residential or commercial. (An organization without users can't do anything)
• A customer is an organization that buys product (transacted by the user within the organization).
• A vendor is an organization that sells product (transacted by a user within that organization).
• A user that buys and sells products is still a single user, belonging to a single organization.
• A broker that buys or sells on behalf of multiple organizations is (design decision) [a unique user for each organization it represents | associated to each organization it represents but is still a single user]

That first bullet is very important. Every action should be associated to a real physical entity. If there is ever a question of who bought/sold something, you should be able to tie it back to the real entity that performed the action. Eventually fraud will occur and you don't want to be the system that dead-ends the investigation because you only associated the order to a generic company record. Make sure you build accountability into your system, even if the company may circumvent it by using shared credentials, etc.

I'm not a big fan of uniqueidentifiers (16 bytes). I'd switch to int (4 bytes) instead. If you expect a very high volume, use bigint (8 bytes) . This also helps provide a human consumable (order, product, etc) id. In my opinion, GUIDs just take up more space and run a bit slower without providing a tangible benefit. Even Amazon uses big integers for their order numbers.

Non-clustered indexes are for performance tuning and are not considered part of the data model. In your question, they just add clutter.

One of the best ways to refine your model is to ask questions of it before you write any code. Go back to your requirements and see if you can generate some questions your model is intended to fulfill.