Advice on database structure
I need some advice on a database.
I have customers, requests, offers, line items and products.
The way it goes is the following:
1. The customer is registered
2. A customer’s request is registered. CU needs a product of specific characteristics let’s say size and price is defined.
3. Based on the specifications I have a found set that I have to go through manually and select the green ones, because color is not an attribute of the products.
4. I want to select from the found set (I have too see each product) and add the selected products to an offer. Send this offer to the customer.
5. The CU either buys one product of the offer or asks me to send further offers in case I have new green products...
This means a customer can have many requests, and each request can have more than one offer, and each offer can contain one or more products.
Relationships look like this:
Problem Nr 1:
I can create an offer from the request page, but after that I should open all the products in a new window which are already filtered by size and price.
Problem Nr 2:
When I select a product to be included in the offer the products id gets copied to the new record in the line items table, but the offer id gets lost, It doesn't know where it is coming from.
I hope you understand what I want to accomplish! :-)
Many thanks in advance!