Design Issue

[acss]

Creating a invoice tracking DB yet there is no standardization from the 7
different formats that customers send in. Some fields are always on invoices
such as number ,date, description, but there are others that have additional
fields that need to be recorded. Should i be creating as many fields to match
all invoices fields from different customers? There will certainly be null
values in some fields while others contain data.

Thanks

 

[acss]

It does not matter is some fields are null yet i wanted to get it right as
far as design.In fact when i create the forms for end users, I will have to
dedicate one form per customer dependant on the fields needed for them. It
should be 7 forms called out by the switchboard with a button dedicated to
each form. Just trying to keep it simple but in a case where invoices have
different fields, i guess this is my only choice.

 

[tina]

well, the danger in that kind of design is always that if you encounter an
8th format (a new customer, perhaps) then you have to add additional fields
to the tables and redesign queries, forms, reports, rewrite code...

what is some of this additional data that must be recorded? how is it used
by your company? more info, please.

hth

 

[acss]

The additional fields that some invoices have that others do not are just for
reference in tracking the actual order. Some can have one reference while
others can have several.In reading postings i can see it will not have a
material impact since as long as there are at least two reference fields
available. I just wanted to understand better in having null values in
records dependant on customer.

Thanks

 

[tina]

well, you could consider that "miscellaneous additional data". i might
handle that by creating a table that lists the descriptions of all the misc
data you might want to store for any invoice (these would be the names you
might otherwise have given to individual fields in the table, to identify
the data). if necessary, you could go so far as to associate each
description with a specific customer. then add a data table as the -many
side of a one-to-many relationship with the invoices table. in that table
you can enter as many "additional data" records as you need for each
individual invoice record. the setup/relationships would follow this
example, as

tblInvoices
InvoiceID (primary key)
InvoiceNumber
InvoiceDate
Company

tblReferences
RefID (pk)
RefName

tblInvoiceReferences
InvRefID (pk)
InvoiceID (foreign key from tblInvoices)
RefID (foreign key from tblReferences)
RefValue (whatever the actual piece of data is)

tblInvoices.InvoiceID 1:n tblInvoiceReferences.InvoiceID
tblReferences.RefID 1:n tblInvoiceReferences.RefID

enter the data in a standard mainform/subform layout, where the mainform is
bound to tblInvoices, the subform is bound to tblInvoiceReferences, and
there is a combobox control on the subform bound to the RefID field in
tblInvoiceReferences, with its' RowSource set to tblReferences.

hth

 

[Tony Toews [MVP]]

The additional fields that some invoices have that others do not are just for
reference in tracking the actual order. Some can have one reference while
others can have several.In reading postings i can see it will not have a
material impact since as long as there are at least two reference fields
available. I just wanted to understand better in having null values in
records dependant on customer.

Having extra fields null isn't a problem. Go for it. I do that all
the time.

But also consider adding a comments field so the users can key in
stuff that simply doesn't belong in any of the predefined fields.

Tony