Kryst51
Singin' in the Hou. Rain
- Local time
- Today, 10:08
- Joined
- Jun 29, 2009
- Messages
- 1,896
Once again I am posting about the design of my nonconformance database. Please find attached my current relationship layout.
Nonconformances occur for all types of reasons. Most of them involve material, some related to a customer, some not, some related to an outside vendor, or some that has been produced by us. Credits and Debits of all shapes and sizes need to be able to be done. Some that are for specific tags on an NCR, while other tags will not be debited. Same thing with the credit, the credit can occur for a specific tag on an invoice, or if there is no material returned a fixed amount may need to be done, etc. Hopefully you can see from this how diverse the scenarios can be.
I have a base idea, where I have a CreditDebitHeader table, which stores the type (Credit or Debit or Freight Claim) and a CreditDebitDetail table, which stores the details. What I am unsure of is how this should link in with the rest of the information, given the necessity for versatility. The report that will be generated needs to have the customer name, invoice number (if applicable), tag numbers (if applicable), item(which is related to the tag) plus various other information such as the NCR number, the date, Return info (if applicable) etc. Plus the credit details. If it is for a specific invoice, grouped by customer, invoice, item. If it is one lump sum for the customer, then grouped by customer only as sometimes there is more than one customer on an NCR, and no particular invoice to reference, or one lump sum and all invoices need to be referenced but not grouped by.
In my current db, I have several tables to handle this that duplicate the same structure, and hold similar information but attach to the other tables at different places. I am hoping there is a better way, but am at a loss as to how to do it. I have been drawing diagram after diagram to try to visualize what needs to happen but just can’t wrap my mind around it.
If anybody can provide a direction to think about I would be grateful. I realize that this is long, and may not be the best way to explain the problem, so please ask me questions if you don’t understand what I am asking.
Edit: One thing I just realized, that I will be changing in my relationships, is that there may not be material involved, but there may still be a customer, or vendor, plus there corresponding invoices or pos given various situations. So this might change the whole relationship screen.
Edit 2: I just realized that I can leave it as it is, as the tag header doesn't need a tag necessarily but can still reference a PO and/or invoice, etc. so I think that will be fine.
Nonconformances occur for all types of reasons. Most of them involve material, some related to a customer, some not, some related to an outside vendor, or some that has been produced by us. Credits and Debits of all shapes and sizes need to be able to be done. Some that are for specific tags on an NCR, while other tags will not be debited. Same thing with the credit, the credit can occur for a specific tag on an invoice, or if there is no material returned a fixed amount may need to be done, etc. Hopefully you can see from this how diverse the scenarios can be.
I have a base idea, where I have a CreditDebitHeader table, which stores the type (Credit or Debit or Freight Claim) and a CreditDebitDetail table, which stores the details. What I am unsure of is how this should link in with the rest of the information, given the necessity for versatility. The report that will be generated needs to have the customer name, invoice number (if applicable), tag numbers (if applicable), item(which is related to the tag) plus various other information such as the NCR number, the date, Return info (if applicable) etc. Plus the credit details. If it is for a specific invoice, grouped by customer, invoice, item. If it is one lump sum for the customer, then grouped by customer only as sometimes there is more than one customer on an NCR, and no particular invoice to reference, or one lump sum and all invoices need to be referenced but not grouped by.
In my current db, I have several tables to handle this that duplicate the same structure, and hold similar information but attach to the other tables at different places. I am hoping there is a better way, but am at a loss as to how to do it. I have been drawing diagram after diagram to try to visualize what needs to happen but just can’t wrap my mind around it.
If anybody can provide a direction to think about I would be grateful. I realize that this is long, and may not be the best way to explain the problem, so please ask me questions if you don’t understand what I am asking.
Edit: One thing I just realized, that I will be changing in my relationships, is that there may not be material involved, but there may still be a customer, or vendor, plus there corresponding invoices or pos given various situations. So this might change the whole relationship screen.
Edit 2: I just realized that I can leave it as it is, as the tag header doesn't need a tag necessarily but can still reference a PO and/or invoice, etc. so I think that will be fine.
Attachments
Last edited: