2 primary keys?

Hi! I'm doing an electronic file indexing program..... tblUSERS connected to
tblCABINETS connected to tblDRAWERS connected to tblFILEFOLDERS connected to
tblHANGINGFOLDERS connected to tblFILES .... whew finally ... anyway ...
Since a user can be assigned multiple cabinets and drawers with multiple
filefolders ... I have to assign a primary key right? Is there a way for me
to have two fields as both primary?? Sorry ... having a hard time with
technical explanation ... what i mean is that want i want is that its ok to
have duplicate drawers for each user as long as the user and drawer field
isn't the same ... I can't assign a primary key for both ...

Thanks ... I hope someone understands my question....

Yup .. I read something about composite keys ... two fields with one key ...
problem is i don't know what type of relationship ... another thing is that
when i open the cabinet folder ... it does not show a plus dropdown where I
can input drawers ...

"Bob Betts" wrote in message
...
Hi! I'm doing an electronic file indexing program..... tblUSERS connected
to tblCABINETS connected to tblDRAWERS connected to tblFILEFOLDERS
connected to tblHANGINGFOLDERS connected to tblFILES .... whew finally ...
anyway ... Since a user can be assigned multiple cabinets and drawers with
multiple filefolders ... I have to assign a primary key right? Is there a
way for me to have two fields as both primary?? Sorry ... having a hard
time with technical explanation ... what i mean is that want i want is
that its ok to have duplicate drawers for each user as long as the user
and drawer field isn't the same ... I can't assign a primary key for both
...

Thanks ... I hope someone understands my question....

Fields are not keys, keys are indexes (indices).

In Jet you can have up to 10 fields in an index including the Primary Key
index.

In SQL Server this limit is pushed up to 16 fields per index max.

In table design select the fields you require and press the Primary Key
button or open the indexes dialog enter the name of your index and then
select the fields on the right hand side one per line. To start a new index
in that dialog enter a new name on the left and then start selecting fields.

From your description I think you should be learning about normalisation and
what Primary Keys are for before you proceed. You seem to be missing
important tables to resolve what appears to be several Many to Many
relationships.

You probably need a UserCabinet table to resolve Users many to many
relationship with Cabinets, and its Primary Key would be a combination of
the fields that make up the Primary Keys in both the User and Cabinet table.

EVERY TABLE IN A RELATIONAL DATABASE SHOULD (MUST) HAVE A PRIMARY KEY

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"Bob Betts" wrote in message
...
Hi! I'm doing an electronic file indexing program..... tblUSERS connected
to tblCABINETS connected to tblDRAWERS connected to tblFILEFOLDERS
connected to tblHANGINGFOLDERS connected to tblFILES .... whew finally ...
anyway ... Since a user can be assigned multiple cabinets and drawers with
multiple filefolders ... I have to assign a primary key right? Is there a
way for me to have two fields as both primary?? Sorry ... having a hard
time with technical explanation ... what i mean is that want i want is
that its ok to have duplicate drawers for each user as long as the user
and drawer field isn't the same ... I can't assign a primary key for both
...

Thanks ... I hope someone understands my question....

Craig Alexander Morrison wrote:
keys are indexes

It's logical vs physical. It would be less confusing to say, in Jet
keys are implemented using indexes. However, I think the OP is confused
enough g, are they really interested in physical implementation
issues?

Jamie.

--

If one is using Access and have little knowledge of Normalistion I don't
think making the distinction between keys and indexes is going to confuse
them less.

However keys are implemented as indexes would have been more correct,
Thanks.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"Jamie Collins" wrote in message
oups.com...

Craig Alexander Morrison wrote:
keys are indexes

It's logical vs physical. It would be less confusing to say, in Jet
keys are implemented using indexes. However, I think the OP is confused
enough g, are they really interested in physical implementation
issues?

Jamie.

--

Whoa ... complicated answers ... yes I agree that I must read more on table
normalization ... anyway ... Yes my understanding is that you assign a field
with a primary key for indexing ...referring back to my question ... What i
want if the same user inputs same cabinet name and drawer name the
application will not allow him or her.



"Craig Alexander Morrison" wrote in
message ...
If one is using Access and have little knowledge of Normalistion I don't
think making the distinction between keys and indexes is going to confuse
them less.

However keys are implemented as indexes would have been more correct,
Thanks.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"Jamie Collins" wrote in message
oups.com...

Craig Alexander Morrison wrote:
keys are indexes

It's logical vs physical. It would be less confusing to say, in Jet
keys are implemented using indexes. However, I think the OP is confused
enough g, are they really interested in physical implementation
issues?

Jamie.

--

On Tue, 23 May 2006 14:37:19 +0800, "Bob Betts"
wrote:

what i mean is that want i want is that its ok to
have duplicate drawers for each user as long as the user and drawer field
isn't the same ... I can't assign a primary key for both ...

To get down to practical nuts&bolts...

In table design view, Ctrl-mouseclick one, two, or up to ten fields
which should jointly constitute a Primary Key for your table.

Click the Key icon.

The selected fields will then each allow duplicates within a single
field, but the combination must be unique, just as you request.

John W. Vinson[MVP]

I am not sure what you are asking but let me try to answer the question I
think you may be asking.

Are you asking how can I design the "database" (as opposed to the
application) so that One Person can only be assigned to One Drawer in any
One Cabinet. I am also assuming that the Person can be associated with many
Cabinet Drawers.

If that is the question then (assuming a Cabinet can have many Drawers, and
Drawers in different Cabinets can have the same name/number)

You need a Cabinet table with say a Primary Key of CabinetName this is
related in a One to Many with Drawers where the Drawer can only belong to
one Cabinet and the Primary Key will be a compound key made up of
CabinetName (the PK from the Cabinet table) and DrawerName. These two fields
CabinetName and DrawerName are the Primary Key of Drawer. (This (the
Compound Key) allows other drawers in other cabinets to have the same Drawer
Name say Miscellaneous)

You state that only one person can be assigned to any given Cabinet/Drawer
so then you include a single field in the CabinetDrawer table to relate to
the Persons table. You include the Primary Key of the Persons table as a
foreign key in the CabinetDrawer table and this ensures that a CabinetDrawer
can only have one (or no) Persons assigned to it.

This rule (One Person to any Given Cabinet Drawer) should be enforced in the
database as opposed to the application. This distinction may seem strange if
all your system is in one MDB file however the Tables and Relationships are
the database and everything else Queries, Forms, Reports etc are the
application. It is fairly common practice to create two MDB files one
containing the application and one containing the database.

Database Design by email is dangerous please check that the assumptions I
expressed at the start are in line with your understanding of the problem
domain.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"Bob Betts" wrote in message
...
Whoa ... complicated answers ... yes I agree that I must read more on
table normalization ... anyway ... Yes my understanding is that you assign
a field with a primary key for indexing ...referring back to my question
... What i want if the same user inputs same cabinet name and drawer name
the application will not allow him or her.



"Craig Alexander Morrison" wrote in
message ...
If one is using Access and have little knowledge of Normalistion I don't
think making the distinction between keys and indexes is going to confuse
them less.

However keys are implemented as indexes would have been more correct,
Thanks.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"Jamie Collins" wrote in message
oups.com...

Craig Alexander Morrison wrote:
keys are indexes

It's logical vs physical. It would be less confusing to say, in Jet
keys are implemented using indexes. However, I think the OP is confused
enough g, are they really interested in physical implementation
issues?

Jamie.

--

Yes ... it seems that you were able to get my question.... someone posted
here about two primary keys ... I tried that too and the relationship became
indeterminate ....

I'm trying to do a file indexing program ... one user ... many drawer ...
per drawer many cabinets ... per cabinet ... mutiple hanging folders ... I
thought of just assigning them a 1-100 cabinets per user ... assigning a
range so they won't use the same number as work around ... or put a
department accronym in each number like IT-001 to make it unique .. but
this solves only for the cabinet and drawer level ... What if someone from
two people from IT uses IT-0001 ... .. hmmmm I'll try to read your post
again...





"Craig Alexander Morrison" wrote in
message ...
I am not sure what you are asking but let me try to answer the question I
think you may be asking.

Are you asking how can I design the "database" (as opposed to the
application) so that One Person can only be assigned to One Drawer in any
One Cabinet. I am also assuming that the Person can be associated with
many Cabinet Drawers.

If that is the question then (assuming a Cabinet can have many Drawers,
and Drawers in different Cabinets can have the same name/number)

You need a Cabinet table with say a Primary Key of CabinetName this is
related in a One to Many with Drawers where the Drawer can only belong to
one Cabinet and the Primary Key will be a compound key made up of
CabinetName (the PK from the Cabinet table) and DrawerName. These two
fields CabinetName and DrawerName are the Primary Key of Drawer. (This
(the Compound Key) allows other drawers in other cabinets to have the same
Drawer Name say Miscellaneous)

You state that only one person can be assigned to any given Cabinet/Drawer
so then you include a single field in the CabinetDrawer table to relate to
the Persons table. You include the Primary Key of the Persons table as a
foreign key in the CabinetDrawer table and this ensures that a
CabinetDrawer can only have one (or no) Persons assigned to it.

This rule (One Person to any Given Cabinet Drawer) should be enforced in
the database as opposed to the application. This distinction may seem
strange if all your system is in one MDB file however the Tables and
Relationships are the database and everything else Queries, Forms, Reports
etc are the application. It is fairly common practice to create two MDB
files one containing the application and one containing the database.

Database Design by email is dangerous please check that the assumptions I
expressed at the start are in line with your understanding of the problem
domain.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"Bob Betts" wrote in message
...
Whoa ... complicated answers ... yes I agree that I must read more on
table normalization ... anyway ... Yes my understanding is that you
assign a field with a primary key for indexing ...referring back to my
question ... What i want if the same user inputs same cabinet name and
drawer name the application will not allow him or her.



"Craig Alexander Morrison" wrote in
message ...
If one is using Access and have little knowledge of Normalistion I don't
think making the distinction between keys and indexes is going to
confuse them less.

However keys are implemented as indexes would have been more correct,
Thanks.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"Jamie Collins" wrote in message
oups.com...

Craig Alexander Morrison wrote:
keys are indexes

It's logical vs physical. It would be less confusing to say, in Jet
keys are implemented using indexes. However, I think the OP is confused
enough g, are they really interested in physical implementation
issues?

Jamie.

--

Thanks ... I'll try this too and see what happens .... wait I think I did
.... the relationship changed to indeterminate??


"John Vinson" wrote in message
...
On Tue, 23 May 2006 14:37:19 +0800, "Bob Betts"
wrote:

what i mean is that want i want is that its ok to
have duplicate drawers for each user as long as the user and drawer field
isn't the same ... I can't assign a primary key for both ...

To get down to practical nuts&bolts...

In table design view, Ctrl-mouseclick one, two, or up to ten fields
which should jointly constitute a Primary Key for your table.

Click the Key icon.

The selected fields will then each allow duplicates within a single
field, but the combination must be unique, just as you request.

John W. Vinson[MVP]