Preliminary design

  • Thread starter Thread starter acss
  • Start date Start date
A

acss

I am in the first stage of creating a DB that will be utilized by six people.
They will be keying data and running queries during each month as invoices
are paid or services performed and invoices expected to be updated later in
the month. In the design i understand there needs to be a table for customers
then another for invoices yet each invoice has an itemized description of
charges. Should there be another table for these charges or are these fields
in the invoice table? There is also an internal expense account that is
applied to the invoice that identifies type of expense. The main purpose of
DB is to track expenses so any advise is appreciated.

Thanks
 
This has to be driven by the specifics of the informaiton that you are trying
to database, and by the details of what you are trying to accomplish. So
far you've given us only three sentences (1/2 of your one paragraph post) on
that, so we only know a little.

Taking your question literally and at face value, I think that you need an
InvoiceItems table, linked to your invoice table.


I suspect that once you really think this through, it's going to get
complicated. Like that you will find that the entities that you are really
tracking are delivery of billable services and payment for them. And that
"invoicing" is a one time transmittal/notification action to a customer, to
be followed by different types of actions (sending of statements) when you
don't receive payments.
 
Thanks. As stated it is a expenses tracking database so for example in
describing the relationships, One invoice can have many description of
charges so yes it would probably be an invoice items table tied to an invoice
table though not sure what the primary key would be. I then would have an
expense account table with account number and acct description as fields in
this table. In this relationship it would be an invoice can only have one
expense account so i assume this would be a one to one relationship from
invoice table to account table. Is this correct?
 
If this is to be a multi-user system related to services and invoicing, is
there a reason you aren't using a commercially-available shrink-wrap
package? Recreating the functionality of something like, say, Quickbooks in
Access is not for the faint of heart...<g>

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks Jeff. This database will be replacing the use of workbooks since it
really is for the purpose of logging expenses and keeping track. In the month
end there is some filtering of all data and sum totals but not much more than
that. There is a need for a simple dtabase file format than excell flat file.
The invoices are not generated but received and information is recorded.
 
Hello ACSS

Would like to help but the important basics are still unclear. You use
terms ("invoice", "services performed" "expense", "updating" of invoice,
"customer") with important you-specific definitions that you've not told us
about. I can't even tell whether you are buying or selling, as your post
seems to conflict with itself on that. You are calling these "expenses" (=
buying) yet are talking about customers and services performed (= selling).

But, without knowing all of that, here's a guess in the dark on your
structure question: (of course feel free to change/shorten my overly long
explanatory field names)

Table Customers:
Record = an entity that these invoice transactions are with
PK (Primary key) = CustomerIDNumber
Plus customer descriptive fields

Table: ExpenseCategories
Record = Standardized expense category
PK = ExpenseCategoryIDNumber
plus category descriptive fields

Table: Invoices
Record = Invoice "header" type informaiotn
PK = InvoiceIDNumber
FK (Foreign Key) ExpenseCategoryIDNumber
FK CustomerIDNumber
plus fields for "one per invoice" type information

Table: InvoiceItems
Record = Invoice items
FK = InvoiceIDNumber
PK (unused) InvoiceItemsIDNumber

Link all fields to their namesakes

Wherever you don't already have real worlding numbersystems in place for the
keys, make the PK fields autonumber types, and FK fields long integer types

Hope that helps a little!
 
To try and povide some clarity.....we receive invoices from outside
contractors and pay for their services performed. The submit invoices and
their is a break down (description of their expenses such as fuel, import
tax, overtime..etc) Each invoice has a unique ID number and never repeated,
contractor name, address, etc. This invoice is paid from us and we assign an
expense account that has a internal description for our purposes. The table
you listed as Expense Categories could be the expense account and description
that is applied to the invoice for payment. Does this help??
 
Hello Acss,

Yes, that clears up all of the big questions and helps a great deal. Based
on that I think that my guessed structure wouuld be what I would recommend as
a starting point. .

To discuss a couple of fine points:

My structure reflected your statement which was, in essence, that a single
expense sccount number would always apply to the entire invoice. I'd double
check that; if so, fine (and less work)......if not, move the FK
ExpenseCategoryIDNumber field from the "Invoices" table to the
"InvoiceItems" table, in which case you'll be assigning expense number to
the individual items.

Another decision is whether to make the Invoice Number be just a field or
make it the primary key (in place of my "InvoiceIDNumber" in those last 2
tables) Long story short, my recommendation would be to make it just
another field (VendorsInvoiceNumber) (shorten that)

Looks like you're on your way!
 
Thanks Fred. There can only be 1 expense account applied to each invoice
regardless of how many individual items on invoice. There fore i expect one
table that has two fields (accountnumber) & (account description) with
account number being PK. In the invoice table i suspect InvoiceID to be the
unique field and it would be PK. Once i move along and create fields
relationships i can post later to see if i am on the right track. Thanks again
 
Back
Top