J
Jack Sheet
Hi all - newbie query:
I am trying to create a database of customers.
Customers are identified by a unique reference.
The format of the reference is
AAnnnnB...
where AA are letters, compulsory, length 2
nnnnn are numbers, compulsory, length 4 (can be leading zeros)
B... is an optional string of letters of indeterminate length (never more
than 5 long in practice although not constrained, often omitted).
OK, no criticisms, please, of the format for customer references. It was
not my decision and the reason is lost in the mists of time, before
electronic databases were a twinkle in Bill's eye. Since then, the number
of customers is so large and the number of dependent systems so convoluted
that it will be easier to accept that format and work around any associated
problems than to try to assign a more sensible method and renumber all of
the clients. At least so I reckon. Humour me on that.
So, on to the problem.
Should I be advised to create separate (a) three separate fields in the
client database in which to reside the prefix ("AA"), the numerical element
("nnnn") and the optional suffix ("B"), or (b) a single key field containing
the entire string, having regard to the following factors:
The "nnnn" element should be sequential, by which I mean that there must be
no missing breaks in the number sequence.
However th "nnnn" element can be duplicated, but only with the same
accompanying prefix but different suffixes.
For example, with the numerical element sorted a section of the the client
references might look like this:
AB1234A
AB1234BH
AB1234BW
MX1235
FG1236B
Given the above, it would not be possible to have a client AX1234 because
although 1234 is permitted it is only permitted with a prefix AB, given that
the number 1234 has already been assigned.
It would not be possible to have a client TH1238 without first assigning a
client with numerical element 1237
It would be possible to assign another reference commencing MX1235 but only
if a suffix is added (normally this would only be done if there is some
relationship with the existing customer reference MX1235).
Ultimately I want to build in some control over the completeness of the
database by checking for duplications of the entire reference, checking for
omitted numerical numbers etc. I might want to set up a query on just the
numerical aspect, and I may want to include data validation at the point of
record creation to ensure that the number is valid.
Have I given enough information for you to advise me whether I should use
three separate fields?
Thanks
I am trying to create a database of customers.
Customers are identified by a unique reference.
The format of the reference is
AAnnnnB...
where AA are letters, compulsory, length 2
nnnnn are numbers, compulsory, length 4 (can be leading zeros)
B... is an optional string of letters of indeterminate length (never more
than 5 long in practice although not constrained, often omitted).
OK, no criticisms, please, of the format for customer references. It was
not my decision and the reason is lost in the mists of time, before
electronic databases were a twinkle in Bill's eye. Since then, the number
of customers is so large and the number of dependent systems so convoluted
that it will be easier to accept that format and work around any associated
problems than to try to assign a more sensible method and renumber all of
the clients. At least so I reckon. Humour me on that.
So, on to the problem.
Should I be advised to create separate (a) three separate fields in the
client database in which to reside the prefix ("AA"), the numerical element
("nnnn") and the optional suffix ("B"), or (b) a single key field containing
the entire string, having regard to the following factors:
The "nnnn" element should be sequential, by which I mean that there must be
no missing breaks in the number sequence.
However th "nnnn" element can be duplicated, but only with the same
accompanying prefix but different suffixes.
For example, with the numerical element sorted a section of the the client
references might look like this:
AB1234A
AB1234BH
AB1234BW
MX1235
FG1236B
Given the above, it would not be possible to have a client AX1234 because
although 1234 is permitted it is only permitted with a prefix AB, given that
the number 1234 has already been assigned.
It would not be possible to have a client TH1238 without first assigning a
client with numerical element 1237
It would be possible to assign another reference commencing MX1235 but only
if a suffix is added (normally this would only be done if there is some
relationship with the existing customer reference MX1235).
Ultimately I want to build in some control over the completeness of the
database by checking for duplications of the entire reference, checking for
omitted numerical numbers etc. I might want to set up a query on just the
numerical aspect, and I may want to include data validation at the point of
record creation to ensure that the number is valid.
Have I given enough information for you to advise me whether I should use
three separate fields?
Thanks