CODEWITHSUNDEEP
sql-server-2008database-designrelational-databasedatabase-schema
Rob Hutchin
Rob Hutchin

Reputation: 79

Over-engineered database design

I am creating a sales application and have a preliminary design of the products and the sales thanks to some help from people on here :)

I am however not sure if I am perhaps over-engineering or could simplify my design slightly.

Below is my product order schema.

Some business rules:

  1. A customer can order one or more contracts
  2. A contract can contain one or more products
  3. A Product can be supplied by one or more Distributor
  4. A product can be supplied by one or more network
  5. A Distributor can sell products from one or more networks
  6. A products price can vary based on the distributor

enter image description here

My questions:

  1. Is my design over-engineered? It seems very complex with its use of link tables and the creation of 3 versions of a product

    • a network product
    • a distributor product
    • the product

    I am trying to enforce the business rules in the database for example if a distributor does not sell network a then products from network a should not be available. Its not enough just to assign the product to a distributor e.g. all products from network a and then just query to see that distributor 1 sell network a but no product from b this is a hard rule and i want to prevent people selecting products they shouldn't?

  2. Assuming i cannot adjust the schema are my 2 link tables NetworkPRoduct and DistributorNetwork best left with a composite primary key that is used in Distributor product or can i some how getaway with a surrogate key such as networkprodutid? Will this still enfore the fact that a Distributor only sells certain networks?

Thanks

Rob

Upvotes: 3

Views: 835

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

A design is overengineered when the complexity costs beyond the point where they are needed to meet your business rules. Nobody can make this determination better than you. Personally I think though it is underengineered.

The problem you are likely to run into is that which distributor the product is to be purchased from may not be known at contract signing time. It looks to me (I may be misunderstanding this) that your schema supposes you know this. So you are likely to need an additional products table out there and a true price matrix for the distributors.

And if your current design seems overly complex to you, I should show you how we model this and how we intend to model it going forward.

Upvotes: 1

Related Questions