Aug 14 2007, 03:48 AM
A quick question on opinions on what would be the best design method to use for the following:
A section of my database handles supplier details and the relevant supplier contracts for supply of raw materials. With reference to these supply contracts, we have 3 - 4 different sets of terms and conditions of supply, based on the category of supplier. When printing the supply contract (report with unique number for the relevant supplier) I would like to attach the terms and conditions which are relevant to that supplier noting that these terms are approx. 1 full page of text. This would most likely be attached as a subreport.
Where would be the best place to store the terms and conditions? i.e should I have different subreports, a new table etc?
Aug 14 2007, 04:10 AM
The terms and conditions apply to each category of supplier. Each supplier is allocated a category. Therefore, there is a one-to-many relationship between categories and suppliers, and there is a one-to-one relationship between categories and terms.
Therefore you need a Categories table. Probably very simple:
Then, in your Suppliers table, you need a Category field.
Therefore, in your report, you can easily retrieve the Terms applicable to the specific supplier. Given that this is a lot of text, presumably in a Memo field, I would not in this case add the Categories table to the query that the report is based on. You probably would be better with a subreport for the terms.
Aug 14 2007, 08:26 AM
Excellent Steve, thanks very much.
All makes sense and I will following your method.