Database design many to many relationships

  • Thread starter Thread starter Terrence Carroll
  • Start date Start date
T

Terrence Carroll

I need to come up with a good design for a database addressing client payment
discrepancies. The main goals that I have are to track the number of issues
generated along with resolution status. I have an output file displaying
payment discrepancies by line item on an invoice (i.e. one invoice could have
many line items). Fields for the output file are as follows:
Customer Name, Account Number, Customer Number, Employee Name, Week of
Service, Invoice Number, Invoice Line Item, Date Payment Posted,Total Open
Account Receivable for invoice, Accounts Recevable Analyst


I figure the output file will be its own separate table. What I need to do
is come up with a way to assign an issue number to each line item. The
problem that I have is each line item on the invoice could theoretically have
more than one issue (i.e. sales tax was misapplied and client was billed at
the incorrect bill rate). Also one issue could apply to multiple invoices.
Therefore I do not think I can use a one to many or one to one relationship
between line item on invoice and the issue #. Can I get some tips on what my
"Issues Table(s)" should look like from a design standpoint or should I have
multiple tables due to the many to many relationship that is apparent in
order to come up with a normalized database design?

Thanks,

Terry Carroll
 
I need to come up with a good design for a database addressing client payment
discrepancies. The main goals that I have are to track the number of issues
generated along with resolution status. I have an output file displaying
payment discrepancies by line item on an invoice (i.e. one invoice could have
many line items). Fields for the output file are as follows:
Customer Name, Account Number, Customer Number, Employee Name, Week of
Service, Invoice Number, Invoice Line Item, Date Payment Posted,Total Open
Account Receivable for invoice, Accounts Recevable Analyst
Click to expand...

You're misunderstanding how Access works!

You do NOT need to pull everything together into one table in order to output
it. Typically you would instead have a table of Customers (the only table
containing the customer name), another table of Employees (the only table
containing the employee name), a table of Invoices (with invoice number,
service date, etc.), a table of InvoiceLineItems related one to many to
Invoices, etc.; the "total account receivable" value would not exist in ANY
table, but would be calculated on the fly. Any Report or Export would be based
on a QUERY, not on a table, and the query would pull data together from
multiple tables.
I figure the output file will be its own separate table. What I need to do
is come up with a way to assign an issue number to each line item. The
problem that I have is each line item on the invoice could theoretically have
more than one issue (i.e. sales tax was misapplied and client was billed at
the incorrect bill rate). Also one issue could apply to multiple invoices.
Therefore I do not think I can use a one to many or one to one relationship
between line item on invoice and the issue #. Can I get some tips on what my
"Issues Table(s)" should look like from a design standpoint or should I have
multiple tables due to the many to many relationship that is apparent in
order to come up with a normalized database design?
Click to expand...

You need a table of Issues (e.g. IssueID 1 might be "Item cost entered
incorrectly", 39 might be "Sales tax was charged although customer was tax
exempt), related one to many to a table of ItemIssues; this would have fields
for the IssueID and for the primary key of the InvoiceDetails table. It would
also have fields recording how this particular issue was resolved (e.g. amount
refunded, resolution date, comments...).
 
John,

I really appreciate the help but the issue that I have is that I already
have the invoice detail data based upon a report extracted from a different
system so I am not really looking to create new table for the invoice data or
calculate any of the invoice related info such as AR balance or have the user
input invoice related data. The main objectives that I have are to try to
maintain statistics on how many new payment issues are generated and how
many issues are resolved, and what the timetable is for resolution. I do not
have the ability or resources to modify the existing system to track the
issues so I figured Access would be good tool to use to accomplish my goals.

For a particular week I may have 5000 invoice line items with either short
payments or over payments (i.e. discrepancies) from a number of different
companies. The following scenarios may be evident within the population of
the 5000 invoice line items:

1. Company may have a payment discrepancies on all invoice line items for a
particular period due to differences in how we and they apply contractually
based terms such as discounts and bill rate factors - I need to have an
issue table that allows me to assign an individual issue # to muliple
invoices. - My initial thought proces was to set up a one to many
relationship between my issues (one side) and the invoice line items (many
side) but I do not think I can do this because
2. Individual invoice line items may have multiple issues that require
different steps to resolve (i.e. customer is exempt from sales tax but never
furnished an exemption certificate (external issue) and customer has a
discount that is not being applied appropriately in our system (internal
issue))

I absolutely do not want to have fields with Issue #1, Issue #2, and Issue
#3 because I know it is not a good relational design and it wastes space.
However, I would like to have the database design set up so that when I have
an update to progress notes for a particular issue that the update would
cascade to all affected line items. Any help you or someone else could
provide would be much appreciated.

Thanks,

Terry Carroll
 
Hello Terrence,

I provide help with Access, Excel and Word applications for a modest fee. I
have provided help with the initial design of many databases. I would like
to offer to work with you to come up with a design for your database that
will work for you. My fee would be very reasonable. Contact me if you want
my help.

Steve
(e-mail address removed)
 
Steve said:
I provide help with Access, Excel and Word applications for a modest fee.
I have provided help with the initial design of many databases. I would
like to offer to work with you to come up with a design for your database
that will work for you. My fee would be very reasonable. Contact me if you
want throw awat money and waste time.

Steve
Click to expand...




Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

John... Visio MVP
 
Back
Top