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