is a primary key necessary? I need to allow duplicates but can't

J

jaffsmom

I have a table that currently has a primary key. I've been asked to redo
this table and have to make it so that the some of the information can be
duplicated. I have to remove the primary key in order to do this. My
question is will that
have an impact on any searches that are done through the database?
 
K

KARL DEWEY

It is necessay for a relational database. Without it why not just use an
Excel spreadsheet?
Sounds like what you need to do is put the additional information that
relates to your key in another table and set the relationship.
Table1
Key - Nuts
- Fruit
- Grains
Table2
Nuts Pecan
Nuts Walnuts
Nuts Peanuts
Fruits Apples
Fruits Oranges
Fruits Peaches
 
D

Dirk Goldgar

jaffsmom said:
I have a table that currently has a primary key. I've been asked to
redo this table and have to make it so that the some of the
information can be duplicated. I have to remove the primary key in
order to do this. My question is will that
have an impact on any searches that are done through the database?
Click to expand...

What's the purpose of the duplicated information? While it's not
absolutely necessary, in Access, that a table have a primary key, the
need to allow duplicates in what was formerly a primary key field
probably means that you need to reexamine your overall table design, and
maybe have two related tables where one table was before. If you'll
describe in more detail what the current table represents, and what has
changed that prompts your questions, we may be able to suggest a better
design.
 
K

Ken Sheridan

There is no absolute requirement in Access for any column(s) to be defined as
the primary key, but it would be unusual not to have one. Any table will
have at least one 'candidate key', even if it is all the columns in the table.

I would suspect, however, that the correct solution to your problem is to
decompose the table into two tables which can be related one-to-many. The
data to be duplicated would thus appear only once in one of the tables, but
could be refernced by any number of rows in the other table. This normalized
solution thus avoids the redundancy of having duplicate data in one table,
and eliminates the possibility of update anomalies arising from the
mredundancy.
 
J

jaffsmom

Thanks all for your help. I have 3 tables that are related.
table 1 - just has index number (PK) ie EBSM/OF-CC
table 2 - has 2 columns - index number and subjectname (PK)
table 3 - has 5 columns - memonumber (PK), indexnumber, memotitle,
memokeywords and memodate.
What I need to be able to do is place one memo (memo number) under 2
different subjectsnames as the memo relates to both subjects. Can this be
done?
 
K

KARL DEWEY

I see two things. Your memokeywords should be in another table like --
Table4
memonumber and memokeywords
Relate table3 one-to-many to table4 using the memonumber as you will have
multiple keywords for each memo.

The subjectname table needs to be like --
Table2
subjectname (PK) and memonumber
Relate table3 one-to-many to table2 using the memonumber

I do not see the purpose for your index numbers.
 
K

Ken Sheridan

I have the same take on this as Karl. Table 1 is related one-to-may to Table
2 on index number. The relationship between Table 2 and Table 3, however,
should be on memonumber by adding a memonumber foreign key column to Table 2
and deleting the index number column form Table 3. You can then reference
the same row in table 3 from as many rows in Table 2 as you wish.

There is another possible scenario, however; that the relationship between
Table 2 and table 3 is in fact many-to-many, i.e. each subjectname can be
the subject of one or more memos and each memo can relate to one or more
subjectnames. If this were to be the case the you would need another table,
Table 2.5, to model the many-to-many relationship. This would have two
foreign key columns, subjectname and memonumber referencing the primary keys
of the other two tables. The primary key of Table 2.5 would be a composite
one made up of the subjectname and memonumber columns as these will, in
combination, have unique values. In this scenario Table 2 is unchanged but
the indexnumber column in Table 3 can be deleted.

As regards Karl's other point about the memokeywords column. I share his
concern regarding this as I suspect that this column contains a data
structure; it may not look much like one but that's what a list of values is.
If so you need to put these values in a separate table, Table 4, with one
keyword per row. The keyword column should contain unique values and thus
can be defined as the primary key. I'd suspect that the relationship with
table 3 is again many-to-many, i.e. each memo can have one or more keywords
and each keyword can relate to one or more memos. In this case you'd need a
Table 3.5 with foreign key columns keyword and memonumber to model the
many-to-many relationship in the same way as above.
 
Top