C
coder
I've been struggling with this design for a while now and I can't seem
to get it right. I need a set of tables to store quotes for custom
built products that need to be quoted at any number of different houses
(which each result in a different price, depending on attributes of the
house). I'll lay out PK, FK columns here as a starting point for
critique. There is a seperate DB/API for querying pricing information
given the specs so that is out of the scope of what I'm talking about
here. Assume I can obtain a price given a product configuration and a
house.
Quotes
=====
Quote_Id
QuoteProducts
===========
QuoteProduct_Id
Quote_Id
QuoteProductOptions
================
QuoteProductOption_Id
QuoteProduct_Id
QuoteItems
========
QuoteItem_Id
QuoteProduct_Id
House_Id
Price (not a FK)
QuoteItemNumericOptions
===================
QuoteItemNumericOptions_Id
QuoteItem_Id
House (products are custom built for a particular house. A single quote
can contain any number of houses.)
========
House_Id
Quote_Id
[Quote Products / QuoteProductOptions]
These are the "product configurations" that are available to be quoted.
[QuoteItems / QuoteItemNumericOptions]
A "product configuration" can be quoted at any house. The assignment of
a product at a particular house results in a price which I am storing
as a QuoteItem. The QuoteItemNumericOptions table is only relevant once
there is a product quoted at a house, so its a child of QuoteItems and
not QuoteProducts.
Now there's a particular requirement which is causing problems for me.
Some product configurations are created specifically for just 1 house
and thus would only have a single QuoteItem row. In this case, a
database design like this would be perfect, where the QuoteProduct
table is merged into the QuoteItems table:
QuoteItems <----- QuoteItemOptions, QuoteItemNumericOptions
(QuoteItems now has the Product_Id rather than QuoteProducts. Also
contains the resulting price and House_Id)
How would you satisfy both requirements? If anyone can follow any of
this or wants to critique anything I'd appreciate it.
to get it right. I need a set of tables to store quotes for custom
built products that need to be quoted at any number of different houses
(which each result in a different price, depending on attributes of the
house). I'll lay out PK, FK columns here as a starting point for
critique. There is a seperate DB/API for querying pricing information
given the specs so that is out of the scope of what I'm talking about
here. Assume I can obtain a price given a product configuration and a
house.
Quotes
=====
Quote_Id
QuoteProducts
===========
QuoteProduct_Id
Quote_Id
QuoteProductOptions
================
QuoteProductOption_Id
QuoteProduct_Id
QuoteItems
========
QuoteItem_Id
QuoteProduct_Id
House_Id
Price (not a FK)
QuoteItemNumericOptions
===================
QuoteItemNumericOptions_Id
QuoteItem_Id
House (products are custom built for a particular house. A single quote
can contain any number of houses.)
========
House_Id
Quote_Id
[Quote Products / QuoteProductOptions]
These are the "product configurations" that are available to be quoted.
[QuoteItems / QuoteItemNumericOptions]
A "product configuration" can be quoted at any house. The assignment of
a product at a particular house results in a price which I am storing
as a QuoteItem. The QuoteItemNumericOptions table is only relevant once
there is a product quoted at a house, so its a child of QuoteItems and
not QuoteProducts.
Now there's a particular requirement which is causing problems for me.
Some product configurations are created specifically for just 1 house
and thus would only have a single QuoteItem row. In this case, a
database design like this would be perfect, where the QuoteProduct
table is merged into the QuoteItems table:
QuoteItems <----- QuoteItemOptions, QuoteItemNumericOptions
(QuoteItems now has the Product_Id rather than QuoteProducts. Also
contains the resulting price and House_Id)
How would you satisfy both requirements? If anyone can follow any of
this or wants to critique anything I'd appreciate it.