Struggle with relationships

[Boto]

I'll try to explain what i want.

I've got a customer (ID,name, adress, etc.)

The customer wants to buy a boat, and i want to send him an offer wich i would like to store in the database also (for future reference)(type of boat, customerID, price, options, etc.).

One customer could also have more than 1 offers (some past validity, some still valid)

(i suppose at this moment i need 2 tables, so one for the customer, one for the offers)

The story continues, cause i want to make the offer also using FM7, so i search for the customer and push a button add offer... FM then takes me to a screen at wich i must first select the type of boat the offer is about (cause we've (unfortunatally) got more then 1 types of boats), when the boat is selected, all the possible opions for that type of boat become visible. In front of the options are checkboxes to select the option. Also the prices automatically popup when an option is selected and you can also see the total price.

At this stage my database becomes more complex, because now i believe i need 1 table wich defines my boats, one table defining all available options, one table defining wich options belong to wich boat at which cost (so that's a n:m with a join).

Okay so now the joined table from the last paragraph contains all the available options, now i want it to be connected to the offer. So in the offer table i define the CustomerID and the offerID, the date, the total price by calculation etc, but the most important which type of boat! because this is also a n:m we need another join table (row).

So we've got:

customer <--> offer <--> row

[color:"white"]xxxxxxxxxxxxxxxxxxxxxxxxxxxx ^

[color:"white"]xxxxxxxxxxxxxxxxxxxxxxxxxxxx |

[color:"white"]xxxxxxxxxxxxalloptions <--> option <--> boat

And now we reach the point that my mind won't cooperate ... Who can tell me where the mistake is in the above piece, or maybe tell me somethin to clear things up. I think the problem lies in the fact in which table i define which boat the customer wants, because that means that fact has to be transported through row to option where the appropriate options are then selected.

Sorry for the big post, but thanks in advance! Also sorry for my terrible English

greetz Boto

[zincb]

I would try the following schema:

Customer table (pk CustomerID)

Offer table (pk CustomerId + OfferID)

OfferOptions table (pk OfferID + OptionID) - this is displayed when viewing offer

Boat table (pk BoatID)

BoatOptions table (pk BoatID + OptionsID) - this is displayed when selecting options for offer

Options table (pk OptionsID)

[Boto]

Thanks for the reply... i changed the situation a bit and worked it out a little further. I ran into a more specific problem and posted it in an other section.

Thanks again

http://www.fmforums.com/threads/showflat.php/Cat/0/Number/106884/page/0/view/collapsed/sb/5/o/all/fpart/1