Help with table schema for a quoting and ordering system

Other SQL Server Topics
1

Looking for a bit of advice from anyone who has created a quoted and ordering system.

In this particular case, the purpose of our system is to allow users to place orders and quotes for bathroom partitions.

At this point, my development team hasn't been able to agree on a solid schema design. Everyone is arguing over it.

This is a very simple relationship with a few tables.

A Project can have many rooms (with bathroom partitions)

A Project can have 1 To N Quotes - which is a selection of 1 to N Rooms. Rooms can be quoted more than once.

A Project can have 1 to N Orders - also a selection of 1 to N Rooms. Rooms can't be ordered more than once.

An order could be linked to a quote, but that relationship is not required to be established by the User This relationship is for the purposes of knowing what quote has been ordered.

The problem is that a quote is just an estimate and it doesn't ever match what is being ordered. Users will often work on the Rooms of a project over a period of time, verify all the measurements, and then place an order. In addition, some users will just order and not ever quote. Forcing the user to always create a quote before ordering is not an option.

As you can probably guess the quotes and orders tables are 90% identical.

Here are the arguments from various members of my team.

  1. One application developer wants to enforce a polymorphic approach on the DB and doesn't like the use of nulls in any table. She is using an ORM (nHibernate). The proposal from the developer is to create an intermediate table that combines all the similar data between quotes and orders and then extend that table with 1 to 1 relationships. For example Table X has 1 to 1 relationship with Orders and Table X has a 1 to 1 relationship with Quotes. This will also speed up development since the code for quoting and ordering screens would be identical.

  2. The other approach is to keep the Orders and Quotes tables separate for the sake of readability (for data warehousing, reporting etc. ). This means that the fields in each table would be 95% identical. This design would allow the tables to evolve in their own separate ways over time. It might also mean that the quoting and ordering screen application code would be redundant as well.

  3. Another polymorphic approach is to make the Orders table an extension of the Quotes table. This means that if a project is ordered a Quote record would be created and an Order record would be created. There would be a 1 to 1 between quotes and orders.

  4. The final option is to make one table (call it "Stakes" or "OrdersAndQuotes") that is used for both,. This table would have a field to identify if it's a quote or an order, but it would make use of nulls appropriately for each use.

IMO, I like option #2 or # 4, because I see no reason for enforcing polymorphic design principles on a database schema. Also why add in a bunch of extra joins when it's not needed. Lastly, nulls are OK.

Any thoughts?

Thanks

2

I would rule out #3 completely. That's the only I don't see any good argument for.

I would go with #4, with the proviso that only views were used for reference, and never the actual table name itself. That allows quotes and orders to have completely different logical structures but still be managed more easily by having a common physical structure. That also dramatically cuts down the number of columns that are always NULL. You'd have at least 2 views, but could easily end up with more ("ApprovedQuotes", "UnapprovedQuotes", etc.).

You'll still have lots of joins since you'll still need to normalize all the supporting data tables. You'd also have another table that linked quotes to orders for those cases when it was applicable.

You'll need a "master" personnel table to store ids for employees who can approve different aspects of quotes.

And so on.

1 Like
3

#4 is also my choice as well

4

Thanks

I had not considered creating a view for orders and quotes that used the same physical table. The ORM should be able to insert and update using the view without an issue..

I just wish I could come up with a good name for that table :slight_smile: So far I have Scenario, Stake, OrderAndQuote, but I don't like either of them.