4
\$\begingroup\$

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:

enter image description here

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
\$\endgroup\$
  • \$\begingroup\$ That is quite the model to review. Perhaps you could ask for a subset of it to get reviewed. \$\endgroup\$ – dfhwze May 31 at 7:43
  • \$\begingroup\$ @dfhwze I've modified the sql code. I've left the entire schema there, but I've only left the sql code containing users, companies and messages. Is that better? Do I ask in another question about the rest? Or do I modify my question here after I got an answer on my first subset? \$\endgroup\$ – JeremyVm May 31 at 7:57
  • 2
    \$\begingroup\$ I think it's a good idea to wait for the review of this question before asking a followup question :) \$\endgroup\$ – dfhwze May 31 at 7:59
  • \$\begingroup\$ Please do not update the code in your question to incorporate feedback from answers, doing so goes against the Question + Answer style of Code Review. This is not a forum where you should keep the most updated version in your question. Please see what you may and may not do after receiving answers. \$\endgroup\$ – Mast Jun 7 at 10:14
  • \$\begingroup\$ A follow-up question is posted as a separate (new) question, linking back to the old (this) question. \$\endgroup\$ – Mast Jun 7 at 10:15
2
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Wes, in your modelling example you mention that a company has 0 or more users. This is in fact not correct. I rechecked my schema and saw that I had an error. There should be a relationship between company and user, where a company always has 1 owner and that owner is a user. I think that by adding that relationship the accountability issue is somehow solved? Also, it's not required for a user to belong to an organisation. A user can be a single entity being simply a customer. A company always has a user as owner and can act as customer (buyer) or as company (seller) \$\endgroup\$ – JeremyVm Jun 6 at 6:41
  • 1
    \$\begingroup\$ I have accepted this as answer as it pointed me out to some issues in my db design and helped me to raise some other questions which I have asked in this question. With regards to the identifiers, I'll keep that in mind. Thx! \$\endgroup\$ – JeremyVm Jun 7 at 10:51
  • \$\begingroup\$ As a seller, a company still requires a user to list the product for sale and may not be the owner. There is no reason to eliminate that relationship. Its more work to allow transactions with a user (buyer) and a company (seller). Better to keep it at the user level, and have the company relationship be through the users involved in the transaction. It also reflects reality better. How will you handle the company business being handled by an employee other than the owner when the company grows or the owner is seriously ill? \$\endgroup\$ – Wes H Jun 7 at 13:54
  • \$\begingroup\$ So the only reason I have company table is to keep track of the products it has? Messaging and Order handling should happen via the users linked to the company. \$\endgroup\$ – JeremyVm Jun 8 at 7:27

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.