Dual Primary Keys

[Guest]

I have an IT-generated database with a non-duplicating primary key that is a
text field- unique inventory part. I now need to design an expanded database
on my own and still require a non-duplicative primary key. However, there are
2 possible fields: the inventory part and the order number. Some items have
the unique part but no order number (was existing in inventory without a
specific order unless I create a dummy) and some have an order number but
with part numbers that are common and not unique. The order number is
alphanumeric, 10 digits.

How can I design the new database to create unique records? Do I need to
create a third field that is made by joining the other two and designate it
as the primary? This new field will be jibberish to all users, so I'd prefer
not to do it this way. How do I design the input forms and reports to show
the two major fields (part name and order number) while maintaining record
uniqueness?

 

[Douglas J. Steele]

Are you sure it makes sense to put both sets of data in the same table?

If you are, you could always use an AutoNumber field as the Id, and keep
your existing two numbers as uniquely indexed fields in the table.

 

[david epsom dot com dot au]

It doesn't seem like order numbers and inventory numbers should
be in the same table: but if they must, you can create a unique
index on the two fields (allowing nulls), as well as indexes
on each of the two fields.

You can't use the unique index (allowing nulls) on the two fields
as a primary key because primary keys can't allow nulls.

So you would use an autonumber field as the primary key.

What are you going to use it for? If it's just a list, it
doesn't need a primary key. If it's not just a list, perhaps
there should be two separate tables.

(david)

 

[Jamie Collins]

you can create a unique
index on the two fields (allowing nulls), as well as indexes
on each of the two fields.

You can't use the unique index (allowing nulls) on the two fields
as a primary key because primary keys can't allow nulls.

Null may not be appropriate here, anyhow. A null value is a placeholder
for a (non-null) value that you expect to arrive some time in the
future, for example a start_date and end_date pair where a null
end_date represents a current period. If you know the value is
'unknown' or 'does not apply' or 'the witness was unresponsive' then
use an appropriate (encoded) value.

This from Celko: "For example, the International classification of
Disease uses 999.999 for miscellaneous illness. It means we have
diagnosed the patient, know that he or she has an illness, and cannot
classify it - a scary condition for the patient - but this is not
quite the same things as a missing disease code (just admitted, might
not even be sick), an inapplicable disease code (pregnancy
complications in a male), an unknown disease code (sick and awaiting
lab results), or an error in the diagnosis (the patient's temperature
is recorded as 100 degrees Celsius, not Fahrenheit)."

So, if we either have a value or we know it is 'unknown' without
reasonable expectation of it becoming available then the 'unknown'
state can be encoded (also not that unknown in both columns would be
absurd and therefore should be prevented):

CREATE TABLE Things (
order_nbr VARCHAR(10)
DEFAULT '{{NK}}' NOT NULL
UNIQUE,

CONSTRAINT order_nbr__values
CHECK (order_nbr = '{{NK}}' OR order_nbr LIKE
'[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'),

inventory_nbr VARCHAR(10)
DEFAULT '{{NK}}' NOT NULL
UNIQUE,

CONSTRAINT inventory_nbr__values
CHECK (inventory_nbr = '{{NK}}'
OR inventory_nbr LIKE
'[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'),

CONSTRAINT order_nbr__inventory_nbr__interaction
CHECK (order_nbr <> '{{NK}}' OR inventory_nbr <> '{{NK}}'),

PRIMARY KEY (order_nbr, inventory_nbr)
);

Of course, you can make things easier on the front end developer (which
could be you <g>) by creating a PROCEDURE (parameter query) to replace
nulls and empty strings with '{{NK}}', pad the values with leading
zeros, etc.

It could be that nulls are appropriate, for example a row can be
inserted into this table before an order is generated, therefore a null
order_nbr is a placeholder for the forthcoming value, then a
'nullbuster' column can be added (note the now nullable order_nbr does
not need to be explicitly tested for null in the CHECK constraint):

CREATE TABLE Things2 (
order_nbr VARCHAR(10),

CONSTRAINT order_nbr__values
CHECK (order_nbr LIKE
'[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'),

inventory_nbr VARCHAR(10)
DEFAULT '{{NK}}' NOT NULL
UNIQUE,

CONSTRAINT inventory_nbr__values
CHECK (inventory_nbr = '{{NK}}'
OR inventory_nbr LIKE
'[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'),

CONSTRAINT order_nbr__inventory_nbr__interaction
CHECK (order_nbr IS NOT NULL OR inventory_nbr <> '{{NK}}'),

nullbuster VARCHAR(10) DEFAULT '{{NK}}' NOT NULL,
CONSTRAINT nullbuster__values
CHECK (nullbuster = IIF(order_nbr IS NULL, '{{NK}}', order_nbr)),

PRIMARY KEY (nullbuster, inventory_nbr)
);

The order_number and nullbuster columns must be kept in synch but again
the database developer could write procedures to assist (perhaps a
calculated column can be created in Access? I really don't know).

Jamie.

--

 

[david epsom dot com dot au]

Null can and does mean many things.

One possible meaning is as a placeholder for a (non-null)
value that you expect to arrive. Other common meanings
are (ii) value does not exist, and (iii) value is not applicable
Historically, nulls have also been zero, false, and empty strings.

Of course, many people think that all nulls should be avoided,
or that multiple null types should be defined, but the debate
was by no means conclusive.

Since I'm not in his shoes, I can't tell why he has only
one table: if, as suggested, he uses two tables, he won't
have nulls.

(david)

you can create a unique
index on the two fields (allowing nulls), as well as indexes
on each of the two fields.

You can't use the unique index (allowing nulls) on the two fields
as a primary key because primary keys can't allow nulls.

Null may not be appropriate here, anyhow. A null value is a placeholder
for a (non-null) value that you expect to arrive some time in the
future, for example a start_date and end_date pair where a null
end_date represents a current period. If you know the value is
'unknown' or 'does not apply' or 'the witness was unresponsive' then
use an appropriate (encoded) value.

This from Celko: "For example, the International classification of
Disease uses 999.999 for miscellaneous illness. It means we have
diagnosed the patient, know that he or she has an illness, and cannot
classify it - a scary condition for the patient - but this is not
quite the same things as a missing disease code (just admitted, might
not even be sick), an inapplicable disease code (pregnancy
complications in a male), an unknown disease code (sick and awaiting
lab results), or an error in the diagnosis (the patient's temperature
is recorded as 100 degrees Celsius, not Fahrenheit)."

So, if we either have a value or we know it is 'unknown' without
reasonable expectation of it becoming available then the 'unknown'
state can be encoded (also not that unknown in both columns would be
absurd and therefore should be prevented):

CREATE TABLE Things (
order_nbr VARCHAR(10)
DEFAULT '{{NK}}' NOT NULL
UNIQUE,

CONSTRAINT order_nbr__values
CHECK (order_nbr = '{{NK}}' OR order_nbr LIKE
'[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'),

inventory_nbr VARCHAR(10)
DEFAULT '{{NK}}' NOT NULL
UNIQUE,

CONSTRAINT inventory_nbr__values
CHECK (inventory_nbr = '{{NK}}'
OR inventory_nbr LIKE
'[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'),

CONSTRAINT order_nbr__inventory_nbr__interaction
CHECK (order_nbr <> '{{NK}}' OR inventory_nbr <> '{{NK}}'),

PRIMARY KEY (order_nbr, inventory_nbr)
);

Of course, you can make things easier on the front end developer (which
could be you <g>) by creating a PROCEDURE (parameter query) to replace
nulls and empty strings with '{{NK}}', pad the values with leading
zeros, etc.

It could be that nulls are appropriate, for example a row can be
inserted into this table before an order is generated, therefore a null
order_nbr is a placeholder for the forthcoming value, then a
'nullbuster' column can be added (note the now nullable order_nbr does
not need to be explicitly tested for null in the CHECK constraint):

CREATE TABLE Things2 (
order_nbr VARCHAR(10),

CONSTRAINT order_nbr__values
CHECK (order_nbr LIKE
'[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'),

inventory_nbr VARCHAR(10)
DEFAULT '{{NK}}' NOT NULL
UNIQUE,

CONSTRAINT inventory_nbr__values
CHECK (inventory_nbr = '{{NK}}'
OR inventory_nbr LIKE
'[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'),

CONSTRAINT order_nbr__inventory_nbr__interaction
CHECK (order_nbr IS NOT NULL OR inventory_nbr <> '{{NK}}'),

nullbuster VARCHAR(10) DEFAULT '{{NK}}' NOT NULL,
CONSTRAINT nullbuster__values
CHECK (nullbuster = IIF(order_nbr IS NULL, '{{NK}}', order_nbr)),

PRIMARY KEY (nullbuster, inventory_nbr)
);

The order_number and nullbuster columns must be kept in synch but again
the database developer could write procedures to assist (perhaps a
calculated column can be created in Access? I really don't know).

Jamie.

 

[Guest]

Thanks to all for the thoughtful replies. My comments, in order:
1. Unless I am just blinded by the previous database that I worked with, I
really do need both sets of data in the same table. This is the general
description table that multiple operations revolve around- status, compliance
efforts, comments from various depts, accounting actions, etc. It is not a
simple list of parts. Until it was expanded, the part number was the primary
key that all operations revolved around and they ran smoothly. Now that
smaller parts in bulk are to be included with duplicative part numbers, the
whole concept has hit a snag and I hoped to just "tweak" the design rather
than start over with autonumbering as the primary key.

2. Jamie,
Your analogy to Disease 999.999 designation is appropriate. There will be
some items for which an order number will never be known, but we need to
guard against missing data (nulls). Thanks for the detailed encoding examples
and the nullbuster- I think I may use that.

3. David,
I have only one table in order to simplify downstream operations - queries,
reports, invoice generating, etc. If I have two tables each with a primary
key, then every command must first reference and join the tables to find the
unique item of a part and order combination. I was simply trying to avoid
that complication by defining the unique "item" in the initial table. There
will be approximately 10 additional tables that reference the primary key in
the first "General Description" table.

I hope these comments make sense. I describe myself as a power user, but
noobie developer. I'm trying to save departmental budget by designing the new
database in Access and only using IT to convert to server-based sequel
application. Am I barking up the wrong tree?

 

[Amy Blankenship]

As you get more into working with this data, you'll find that the
"simplified" structure makes queries, forms, etc. downstream harder, not
easier. Defining the relationship in the relationships window and using the
query builder and built in tools will take most of the hassle out of using
an actual normalized structure. For more on normalization and why you might
want to go ahead and do it, check out
http://www.webmonkey.com//templates/print_template.htmlt?meta=/webmonkey/99/13/index1a_meta.html

HTH;

Amy

 

[Guest]

This is the URL for a model of orders and parts. It is a good example of how
data gets split into a variety of tables, and how those tables relate to each
other:

http://www.databaseanswers.org/data_models/orders_and_parts/index.htm

Microsoft also has some ready-made templates for tracking orders:

http://office.microsoft.com/en-us/templates/TC010185471033.aspx?CategoryID=CT011366681033
http://office.microsoft.com/en-us/templates/TC010185481033.aspx?CategoryID=CT011366681033

It sounds like you have had very little experience in designing databases,
and it can be helpful to see what a normalized functioning database looks
like. It is deceptively easy to start making tables in Access, without
needing to know some of the theory and practice behind creating a database.
It would be quite worth your time to do a little bit of study before jumping
in much further on your design.

 

[david epsom dot com dot au]

I'm not going to try to dictate database design by
remote control, but

then every command must first reference and join the tables

Create a query ('view') that joins the two tables and use
that instead.

Of course if, in the end, you find that you have always used the
view and never used the native tables, then you haven't gained.

But on the other hand, the general opinion is that
two tables is normally better for this kind of thing.

(david)

 

[Jamie Collins]

Null can and does mean many things.

Well, some people store comma-separated vales in a single column and
that means something to them but it's still a 1NF violation.

My point was not that 'all nulls should be avoided'; rather,
considering we have nulls in SQL, we should use them appropriately.

A design that uses a null value to mean one of a number of things
('temporarily missing', 'never existed', 'is not applicable', '
intentionally left blank') without differentiating between them is
weaker than one that explicitly encodes each state.

Jamie.

--

 

[Guest]

It sounds like you have had very little experience in designing databases,
and it can be helpful to see what a normalized functioning database looks
like. It is deceptively easy to start making tables in Access, without
needing to know some of the theory and practice behind creating a database.

Thanks to the input here, I have had an epiphany. The existing database
created by IT is not at all normalized and I have been trying to build off an
inferior design. In the existing database, the unique part number is the
single primary key. It is repeated as the primary key in each of the 10
tables. While easy for a user to locate information without writing a query,
this is not as efficient as what you guys are advocating. I think I may
purchase a couple of books and see if I can start off on better footing by
redesigning the entire thing.

 

[Guest]

Thanks to the input here, I have had an epiphany. The existing database

created by IT is not at all normalized and I have been trying to build off an
inferior design. In the existing database, the unique part number is the
single primary key. It is repeated as the primary key in each of the 10
tables. While easy for a user to locate information without writing a query,
this is not as efficient as what you guys are advocating. I think I may
purchase a couple of books and see if I can start off on better footing by
redesigning the entire thing.

There are a number of Idiot's Guide type books about Access, which are good
for just getting your feet wet. For guides on database design, I would
recommend Hernandez' book, Database Design for Mere Mortals (now in 2nd
edition), and O'Reilly's Access Database Design & Programming. The one main
reference book (and I emphasize that it is for reference) would be Microsoft
Office Access 2003 Inside Out.

If you have worked with spreadsheets, you will have to relearn everything
you think you know about data management.

 

[david epsom dot com dot au]

I'm not qualified to enter into a theory debate about
database system meta design: I'm just pointing out that
the ideas you are repeating are not universally accepted.

Certainly Codd suggested differentiating between two
different kinds of null, and there was a counter proposal
to not allow a null type at all,

but the ISO Standard is to have one kind of null, which
represents both of Codd's differentiated nulls, all
three of the null types I mentioned, and several other
null types as well.

That is, some qualified people agree with you that a design
that does not differentiate between null types is weaker
than one which explicitly encodes each state: some qualified
people do not agree with you.

I note that there is a stack of PhD and published literature
on this question: it is unlikely that there is any one other
than you qualified to debate the ideas in
microsoft.public.access.tablesdbdesign.

(david)

 

[Jamie Collins]

I'm just pointing out that
the ideas you are repeating are not universally accepted.

That is, some qualified people agree with you that a design
that does not differentiate between null types is weaker
than one which explicitly encodes each state: some qualified
people do not agree with you.

Thank you for taking the time to point that out.

Jamie.

--