2 primary keys?

[Bob Betts]

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....

 

[Bob Betts]

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 ...

 

[Craig Alexander Morrison]

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

 

[Craig Alexander Morrison]

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.

 

[Bob Betts]

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.

 

[John Vinson]

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]

 

[Craig Alexander Morrison]

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.

 

[Bob Betts]

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...

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.

 

[Bob Betts]

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

 

[John Vinson]

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

Since you don't specify what fields you used in either table, what you
defined as the key, and how you created the relationship, all I can
say is "well, you did something wrong".

IF you...

Ctrl-click the two fields in the "ONE" side table in design mode and
select the Key icon;

and

in the relationships window, select BOTH fields in the joining-fields
window, matching like to like, and then save the relationship,

you shouldn't have this problem.

John W. Vinson[MVP]

 

[Bob Betts]

Thanks John ....I missed the dragging both part in the relationship table
......

I've read a couple of books and never encountered this ... Newsgroups sure
are very beneficial ... real scenarios ... real solutions ....

 

[Bob Betts]

Hi John ... your instructions did work from the first table ... but
connecting to succeeding tables confused me.....

Table 1 Table 2
Table 3 Table 4

tblUSERS tblCABINETS tblDRAWER
tbFILES

USERNO USERNO CABINETNO
DRAWERNO
USERFNAME CABINETNO DRAWERNO
FOLDERCODE1
USERMIDINITIAL CABDES
FOLDERTITLE
USERLNAME CABLOCATION
ACTIVEYR
USERID
INACTIVEYR
PASSWORD
INACTIVELOC
USERDEP
REMARKS
USERDIV

I was able to connect table 1 to table 2 ... Set USERNO as primary key then
went to Table 2 and Set both USERNO and CABINETNO as PRIMARY and dragged
them in the relationship screen... IT worked!!!!! Problem starts in Table 3
....... since Table 2 is connected to table 3 via CABINETNO ... I can't do
the same right? For every cabinet there are multiple drawers and multiple
drawers with files .. no problem with files since the would be unique ....

Do I click CABINETNO and DRAWER with keys and drag them to CABINETNO in
table 2?

Thanks.