473,695 Members | 2,474 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

What to use for primary key of a many-to-many table?

Hi.

What is the usual and what are the possible fields to use for the primary
key of an intersecting table of a many-to-many relationship?

I would think the typical, most common fields would be to set the
intersecting table to have its own unique primary key. Right?

OR

It was suggested using the 2 foreign keys from the two other tables.
Comments?

OR

How about including a date/time field in the primary key for history
tracking ?

Thanks....
Please email me too if possible.

Fred
Nov 12 '05 #1
4 9164
I usually use a regular good old regular autonumber?

There is no need for anything else...is there?
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn
=
Nov 12 '05 #2
fg************* **@yahoo.com (Fred) wrote in
<bj************ @ID-76446.news.uni-berlin.de>:
What is the usual and what are the possible fields to use for the
primary key of an intersecting table of a many-to-many
relationship ?

I would think the typical, most common fields would be to set the
intersecting table to have its own unique primary key. Right?

OR

It was suggested using the 2 foreign keys from the two other
tables. Comments?

OR

How about including a date/time field in the primary key for
history tracking ?


Well, it all depends.

To me, the most common PK for a many-to-many join table would be
your option 2: a unique index on the two foreign keys.

I would only use an Autonumber surrogate key (your option 1) when I
needed to store the join table's PK as foreign key in another
table. This happens suprisingly often.

Your option 3 has nothing to do with determining the primary key,
and really is only about what other attributes the join between the
two tables happens to have in your data schema. If you were
thinking that a date/time field along with the two foreign keys
might give you a PK, I'd say you probably have a schema design
error. The real structure you should use is to have the join with
no attributes of its own, and then have the multiple date/time
records in a table attached to the join table.

I've had the kind of structure in many apps, where a join table
establishes a relationship between two entities and then a table
related to that join records the history of that relationship over
time, with multiple attributes.

In that case, I'd give the join table it's own AutoNumber surrogate
primary key and store that AutoNumber in the related history table.
I would, however, retain the unique index on the two foreign keys
in the join table -- even though they no longer function as PK in
the schema, they should still be unique.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #3
For me, it depends.

If the junction table is further involved as the one in a one to many, then
I'll add an autonumber as the primary key (but also put a unique index on
the combination of the 2 foreign keys).

If it's not involved, then I'll just set the 2 foreign keys as the primary
key.

--
Joan Wild
"Fred" <fg************ ***@yahoo.com> wrote in message
news:bj******** ****@ID-76446.news.uni-berlin.de...
Hi.

What is the usual and what are the possible fields to use for the primary
key of an intersecting table of a many-to-many relationship?

I would think the typical, most common fields would be to set the
intersecting table to have its own unique primary key. Right?

OR

It was suggested using the 2 foreign keys from the two other tables.
Comments?

OR

How about including a date/time field in the primary key for history
tracking ?

Thanks....
Please email me too if possible.

Fred

Nov 12 '05 #4
RE/
I would think the typical, most common fields would be to set the
intersecting table to have its own unique primary key. Right?

OR

It was suggested using the 2 foreign keys from the two other tables.
Comments?

OR

How about including a date/time field in the primary key for history
tracking ?


Not having any extra personal processing power to spare, when I create a table I
create a (usually autonumbered) numeric field as the PK. Period. Just a plain
old dumb number.

Hasn't failed me yet and the few times I haven't done it I've wished I did.

OTOH when I look at some of the multi-field joins some people have to do to get
the same results I get really confused really fast.
-----------------------
PeteCresswell
Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
5141
by: t53864 | last post by:
I need to reorder the primary key of a table. Say, Primary Key (field1, field2) changed to: Primary Key (field2, field1) Would like to know if this creates any problem to the database?
29
2506
by: asj | last post by:
Can you guess what this is? http://www.blueboard.com/phone/apache_sept_2003.gif It's a history of the IIs "Titanic", which is being slowly and painfully sunk by the open source Apache web server. In September, Microsoft's IIs web server again continued to lose marketshare dramatically to the open source Apache web server.
2
3745
by: arijitchatterjee123 | last post by:
Dear All, I am new in MS Sql Server.I am facing a error "'PRIMARY' filegroup is full".I went through the all previous submitions.But still this error is coming.Please help me out. I have Installed Microsoft Sql Server 2000. It was working perfectly. Present Database Details ======================== ..mdf size 3.91 GB from Explorer and from Enterprise Manager Space Allocated 4007 MB
11
2612
by: Geoff Jones | last post by:
Hi Can anybody help me with the following problem? I have a datasource i.e. some data, which I'm accessing via some VB. This I have done; with the help of you guys. However, the original data, and so the dataset I'm working with, does not have a primary key column. I would obviously like to add one. What is the easiest way to do this? My first idea was to simply add a column, which as far as I can see will be at the end of the...
6
8962
by: Emma Middlebrook | last post by:
Hi there, I have created an XmlSchema to represent the tables in my database and I have specified a primary key for each of the tables. However, to be able to execute the following code: DatabaseTables.CategoryInfoRow categoryRow = m_dtCategories.Rows.Find(selected.category_ID) as DatabaseTables.CategoryInfoRow;
9
3389
by: Steven C. | last post by:
Hello: I'm getting an error, "primary key not defined" when trying to use the FIND method on the DataTable Rows collection. I have a typed dataset called 'MortgagesDS' that I created with the XSL builder tool in .NET. I define the dataset at the beginning the the main class: MortgageDS _MortgageDS = new MortgageDS();
115
6232
by: LurfysMa | last post by:
Most of the reference books recommend autonum primary keys, but the Access help says that any unique keys will work. What are the tradeoffs? I have several tables that have unique fields. Can I use them as primary keys or should I define an autonum primary key? One table has information about the 50 states in the US. The table looks like this:
3
20991
by: sandy | last post by:
I cannot figure this out. I have the following code: <code> Private: /****************************************************************************** CreateList
6
5319
by: Lawrence Spector | last post by:
I ran into a problem using g++. Visual Studio 2005 never complained about this, but with g++ I ran into this error. I can't figure out if I've done something wrong or if this is a compiler bug. Here's a very simple example which should illustrate what I'm doing. #include <iostream> template <class T> class TestBase {
2
7577
by: Kvara prasad | last post by:
For creating a user database three important files named primary, secondary & transaction log files. so my question is 'what is the difference between primary & secondary files?' and is 'what is the necessity of secondary files?'
0
8649
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8586
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9137
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8838
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7684
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4594
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3025
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2289
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1986
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.