John said:
I'm under the impression that there is only one primary key per table
in an Access database.
I've got a class project where the professor asks to create a table of
music categories. He tells me what categories are used (6 total) and
he then explains that "the primary key for each category should be a
short code consisting of letters. Another field will be a description
of the category".
I am completely lost as to what the professor is expecting. Can
anyone shed some insight as to what he is expecting me to create?
Please no flames about me going and asking the professor to be more
clear...i don't want to look like a complete idiot.
it's very typical insistence to have a particular primary key or key value
that's built on an ID.
So, for example you might have a primary key as an id for list of country
codes.
thus your table might look like the following
Field Name ID (primary key)
Field name Country (text)
hands the list of values in a table might look like
id Country
1 Canada
2 United States
3 France
it should be noted that it's not really necessary in the above table list of
countries to use an auto number or a lady field as the primary key. We could
just as well use one column and make the text field country the primary key.
thus we can actually have a table that's just as follows
Country
Canada
United States
France
so it turns out that we don't have to use an automatic ID generation system
to build our primary key. often a table will have what we call is an natural
primary key. Thus we have to assume that no two countries will have the same
name and therefore we can actually use the name of the country for the
primary key.
Often it is still preferable to use some type of auto number ID for the
primary key and then use a text field for the description of display of our
data. There is many reasons that come into the design process for choosing
one approach or the other.
For example I might have a part number system or am manufacturing new parts
and thus instead using the part number, I will use an internal ID number.
id PartNumber PartDescripiton
The above table allows us to have an internal auto number that will be used
for my internal relationships. The relationship process will never be seen,
used, or noted by my end users.
The advantage of this approach is that my database can continue to function
until an actual part number is perhaps generated. In some environments we
don't get a part number or product ID generated right away. Perhaps we
produce barcodes and the approval process might take months delay of time
until an actual bar code number for the product is generated.
However you mean to say that my whole database system is to collapse and
stop working because I don't have a staff's social insurance number yet? Or
I don't have a government approved product code bar number yet? That's just
ridiculous to summa that my database should cease to function because I
don't have a part number yet. Furthermore I can remove the product code
number and my database will continue to function just fine.
My point here is that often while an actual part number or social insurance
number or in the case of your categories the actual text of the category
might be an individual or separate or unique category, there's still often
advantages to using another separate system generated number).
So in your case instead of using a system automatically generated number ID
the professor is asking you to use a few charaters (letters) as a primary
key. Thus your table might look something like the following
CATID (pk) Catagory
EST Eastorn Cataglory
WST Wester Catagory
NOR North
As you can see your primary key does not have to be a number or ID. It can
be in fact be a text data type field. (this also means that you to your
users will have to come up with a primary key when you add a new category,
or you come up with some naming scheme yourself in the first place. it
simply means that you'll have to enter and create your own primary key
yourself).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
for my applications as a general rule if and when you have a list of things
like favorite color or just color or whatever I can to still use an auto
number generated by primary key in addition to the text description (and I
even do this when the category is in fact unique). As mentioned I do this
because often I might wanna change the category name, or maybe I don't have
the category yet (when you use an auto number of D then you don't have to
have the category yet or you can change it and your relationships will
continue to function as normal.
From my point of view when I design a database I simply want a say that the
invoices table is related to the customers table. Why should I have to use
any brain cells or any thought or any effort or any design or wasted
developer time for now what field should be used for the relationship here?
should I use the customer social insurance number, should I use some credit
card number? my goodness we could come up for days and days of thinking what
we should use for this relationship. golly one of forget about eight is
garbage set up an auto number primary key system who say here's a relation
to raw the line you done next problem please.
