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
  • 172 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by Airshow
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 : http://www.singingeels.com/Articles/Understanding_SQL_Many_to_Many_Relationships.aspx

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.