Adding a 'Quotation' table - how best to?

[stonward]

My user now wants to store (for a few months at least) QUOTATION data. I can add a table of course, with a unique ID for each quote, but it seems to me that all the other fields (bar the date) can be derived from the other tables...if i put the relevant fields in the Quote table they will be duplicates won't they? Won't this compromise my normalization efforts? Anyone got an idea of how best to crack this little item? Thanks,

Stonward

[missinglinq]

A little more info on exactly what you're trying to accomplish, as well as your current data structure, might help! Is this a price quote for a job/service? If so, where is the data on this stored? Does it have a unique identifying number/code? Why do you feel the need for a separate quotation table? Every developer's situation is unique, but I can't imagine needing a table simply to hold this one piece of information plus a single date.

You're correct in thinking that you don't want to store redundant data any more than is absolutely necessary! You need some Primary/Foreign keys to join this information together. As I said, if we need a little more about your actual situation , we may be able to better advise you!

Welcome to TheScripts!

Linq ;0)>

[FishVal]

My user now wants to store (for a few months at least) QUOTATION data. I can add a table of course, with a unique ID for each quote, but it seems to me that all the other fields (bar the date) can be derived from the other tables...if i put the relevant fields in the Quote table they will be duplicates won't they? Won't this compromise my normalization efforts? Anyone got an idea of how best to crack this little item? Thanks,

Stonward

Use FKs instead of data duplicates.
Does this make a sence? :)

[stonward]

Thanx for replying so quickly guys! I'm modernising a database system I built a couple of years ago. It's a basic stock system with sales and purchasing ability. Now we need to add a quotation facility...I'd already built the form when the (main) user came to me asking that the data be saved. This is costings, (cost per item, VAT, total), quantity, PRODUCT ID (and details) and customer ID (and details) if known. Obviously I can't use the Sales/Purchase tables, so I couldn't see any other way than to add a table.

With reference to the FK idea, I'm not sure how that would help?

My tables are linked of course, with Products linked to Orders and Order Details (to get over the many to many problem) and likewise with Purchases. Suppliers and Customers have a simple one to many with the Orders/Purchases tables.

Could I just use a report, via a query, using a table to save just the quote ID and date perhaps?

[FishVal]

Thanx for replying so quickly guys! I'm modernising a database system I built a couple of years ago. It's a basic stock system with sales and purchasing ability. Now we need to add a quotation facility...I'd already built the form when the (main) user came to me asking that the data be saved. This is costings, (cost per item, VAT, total), quantity, PRODUCT ID (and details) and customer ID (and details) if known. Obviously I can't use the Sales/Purchase tables, so I couldn't see any other way than to add a table.

With reference to the FK idea, I'm not sure how that would help?

My tables are linked of course, with Products linked to Orders and Order Details (to get over the many to many problem) and likewise with Purchases. Suppliers and Customers have a simple one to many with the Orders/Purchases tables.

Could I just use a report, via a query, using a table to save just the quote ID and date perhaps?

Sorry, this doesn't make a sence.
Would you be so kind to post tables metadata. I think you should revise table relationships.