Autonumber in Design

[jk]

TABLE-Customer (one-to many--invoices)
CusCtrID -Primary Unique
CusNum
CusName
CusCity

TABLE-Invoice
InvID--Primary Unique
InvDate
InvDecrip
InvAmt
CusCtrID- FK
ExpenseAcctID- FK

TABLE- EXPENSE ACCT (one to many-invoices)
ExpenseAcctID--Primary Unique
AcctDescrip

I have the above 3 tables and i am trying to track the expenses that hit the
customer center ID which is actually an expense center. I am tring to avoid
using autonumbers in any field since i already have unique ID . A customer
ctr can have many customers but only one customer can be assiged to a
center.An invoice can only have one expense acct per invoice but one expense
acct can be on many invoices. The autonumber will work in this design but i
want to avoid using it, is that possable?

 

[Scott McDaniel]

TABLE-Customer (one-to many--invoices)
CusCtrID -Primary Unique
CusNum
CusName
CusCity

TABLE-Invoice
InvID--Primary Unique
InvDate
InvDecrip
InvAmt
CusCtrID- FK
ExpenseAcctID- FK

TABLE- EXPENSE ACCT (one to many-invoices)
ExpenseAcctID--Primary Unique
AcctDescrip

So ExpenseAcct is, basically, a lookup for Invoice, as is the Customer table ... the Invoice would be your "top level"
entity, and Expense and Customer would simply be properties of the Invoice? That sounds correct, in which case your
design would be fine.

If Customer is NOT an actual "customer" but is instead an "expense center" (whatever that is), then you could have some
issues ... but we don't know enough about your data to say definitively if that's the case.

I have the above 3 tables and i am trying to track the expenses that hit the
customer center ID which is actually an expense center. I am tring to avoid
using autonumbers in any field since i already have unique ID . A customer
ctr can have many customers but only one customer can be assiged to a
center.

Is this correct? Or do you mean "a customer can be assigned to only one Center"? Just sounds odd to me ... but I'm
probably wrong.

An invoice can only have one expense acct per invoice but one expense
acct can be on many invoices. The autonumber will work in this design but i
want to avoid using it, is that possable?

If an Autonumber PK would work, then so would any other PK so long as it's unique. That's all an Autonumber is - a
guaranteed unique value - so I don't see where the problem lies, unless you've got other design troubles.

 

[jk]

"A customer can be assigned to only one Center" is correct. The customers
expenses are charged with the various expense accounts for services provided
which in turn to to thier expense center. Customers can be assigned to only
one expense center.The invoices that are created can only have one account
per invoice but once again since i have a unique 5 number expense center,
unique 4 expense accounts , unique invoice numbers. Is there a way to avoid
using an autonumber since in the event of a future audit, autonumbers have a
tendency to skip?

 

[Scott McDaniel]

"A customer can be assigned to only one Center" is correct.

So you have a single Customer Center, which can have multiple Customers? If that's the case, it would seem to me that
you would need a table to store info about the Customer Center, and then you would associate a Customer with a
particular CustomerCenter. YOu may already have this in place, but your example doesn't show this.

The customers

expenses are charged with the various expense accounts for services provided
which in turn to to thier expense center. Customers can be assigned to only
one expense center.The invoices that are created can only have one account
per invoice but once again since i have a unique 5 number expense center,
unique 4 expense accounts , unique invoice numbers. Is there a way to avoid
using an autonumber since in the event of a future audit, autonumbers have a
tendency to skip?

As long as your various IDs for the tables are unique, you should have no issue using them in place of Autonumbers. The
AutoNumber datatype is really more of a convenience than anything else ... and you're right, it's NOT guaranteed to be
sequential, merely unique.