Member Avatar for daveomcgee

I'm mapping out an entity relationship diagram to try learn more about database design. I currently have approximately 17 tables that map out a quotation management system that quotes customers prices for products and/or services. I've been working on it for a number of days now but I am unable to find resources/help relating to the following

The entities/tables within the scenario I'm having trouble with are as follows:

  • Customer
  • Quote
  • QuoteItemListing
  • ProductItems
  • ServiceItems
  • Products
  • Services

Relationships
Customer (requests) Quote
Quote (has) QuoteItemListing
QuoteItemListing (may have) ProductItems
QuoteItemListing (may have) ServiceItems
ProductItems (are) Products
ServiceItems (are) Services

Essentially, I wish to be able to reference the IDs of the Products and Services a customer wishes to get a quote on in one table (QuoteItemListing). Could anyone please point me in the right direction? Thanks a lot.

My tables at present:

Customer
ID
name
address
phone

Quote
ID
customerFK

QuoteItemListing
ID
quoteFK

ProductItems
ID
productFK
quantityRequired
quoteOrderFK

ServiceItems
ID
serviceFK
quoteOrderFK

Product
ID
name
price

Service
ID
name
hourlyRate
hoursRequired

Edited by daveomcgee because: n/a
  • 2 Contributors
  • 1 Reply
  • 181 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by Airshow

Dani AI

Generated

As noted, the core decision is how to model a "quote line" that can point to either a product or a service. Two practical, maintainable patterns work well: (A) a single unified catalog table that both products and services feed into (recommended), or (B) a quote_line with separate nullable FKs (or a UNION-based query) that enforces "exactly one" reference. Either approach solves the many-to-many / optional-relationship issue, but they trade off simplicity, query ergonomics, and referential integrity.

A - catalog (preferred): put shared attributes in a single catalog table, keep product/service-specific details in child tables, and make quote lines reference the catalog id. This makes queries simple and lets the quote snapshot price/description on the line.

CREATE TABLE catalog_items (
  id INT PRIMARY KEY,
  item_type VARCHAR(20), -- 'product' | 'service'
  name VARCHAR(200),
  current_price DECIMAL(10,2)
);

CREATE TABLE quotes ( id INT PRIMARY KEY, customer_id INT, created_at DATETIME );

CREATE TABLE quote_lines (
  id INT PRIMARY KEY,
  quote_id INT,
  catalog_item_id INT,
  quantity DECIMAL(10,2),
  unit_price DECIMAL(10,2), -- snapshot
  line_total DECIMAL(12,2)
);

Fetch a quote with one join:

SELECT q.id, ql.id AS line_id, ci.name, ql.quantity, ql.unit_price, ql.line_total
FROM quotes q
JOIN quote_lines ql ON q.id = ql.quote_id
JOIN catalog_items ci ON ql.catalog_item_id = ci.id
WHERE q.id = ?;

B - nullable-FKs / UNION: keep product_id and service_id on quote_lines and enforce "one or the other" with a CHECK or trigger. Queries then use LEFT JOIN + COALESCE or a UNION when product/service columns differ.

SELECT ql.id, COALESCE(p.name,s.name) AS item, ql.quantity, ql.unit_price
FROM quote_lines ql
LEFT JOIN products p ON ql.product_id = p.id
LEFT JOIN services s ON ql.service_id = s.id
WHERE ql.quote_id = 123;

Practical tips: always store unit_price and a short description on the quote line so historical quotes don't change when catalog prices change; prefer the catalog approach for easier queries and single-FK constraints; if starting out, follow 's advice and implement PRODUCTS first, verify queries, then add SERVICES. Index your FKs and add constraints/triggers where the DB cannot express the rule.

Member Avatar for Airshow

Dave,

There are two aspects of your E-R schema that you need to learn in order to successfully perform queries.

Firstly, your Product_Items and Service_Items are effectively many-to-many link tables.

Secondly, there is an optional relationship between Quote_Item_Listing and Product_Items/Service_Items.

Both aspects require their own particular SQL to perform both update and select queries. Fortunately, documentation (including tutorials) abounds.

A quick web search found this primer on many-to-many :

For optional relationships, update queries are reasonably straightforward but for select you will typically need to employ a UNION of two separate queries. Try a web search for "SQL UNION".

I think you have set yourself a reasonably challenging task and you might consider a strategy for developing the necessary SQL. Personally, I would simplify things by getting it working for PRODUCTS, ignoring SERVICES (or vice versa). When you get it working, then expand to cater for the other table.

Finally, E-R/SQL solutions are seldom unique. I'm sure you will get differnet advice from other people.

Good luck.

Airshow

Edited by Airshow because: typos
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.