Hi,
Do Junction Tables need ID fields with Primary keys? For example if you
store employee ID, employee name, Company ID, Company name. As you can
see no field here can be a primary key since all of the columns will
have repeatable values. So does one need to put in an autonumber field
and make it a primary key?
thanks 5 2771
assuming
many company IDs, many employee IDs
assigned employee IDs
tblCompany
companyId
companyName
tblEmployee
pkId (autonumber)
employeeId
employeeName
companyId
lets you change the employeeId if required
I assume pkid is just a column with no value other then to have it be
an autonumber primary key correct? Anotherwords you recomend having
primary keys in all tables? Will it be practical to do this even though
I may delete some records from these columns in the future?
While you can use an Autonumber field, you really want employee id and
company id BOTH to be at least a unique index, to prevent duplicate entries
from being insert. As well, since Employee Name and Company Name can be
derived when you know the Ids, they shouldn't be stored in the Junction
table.
--
Doug Steele, Microsoft Access MVP http://I.Am/DougSteele
(no e-mails, please!)
"ken" <ge****@gmail.c om> wrote in message
news:11******** ************@o1 3g2000cwo.googl egroups.com... Hi, Do Junction Tables need ID fields with Primary keys? For example if you store employee ID, employee name, Company ID, Company name. As you can see no field here can be a primary key since all of the columns will have repeatable values. So does one need to put in an autonumber field and make it a primary key?
thanks
An autonumber primary key is unlikely to be an encumbrance or to create
slowdown for a linking (or junction) table.
It could be useful when we want to classify the relationship link.
That is we might have another table categorizing employer-employee
relationships, perhaps something like, hourly, eligible for vacation
pay, eligible for severance, health benefits, whatever. The primary key
of the employer-employee table could then be used in another table
linking that to the employer-employee relationships table in a many to
one mapping. (A one to many relationship is quite impossible.)
In some cases bound forms and recordsets will not update unless a
unique index exists and can be found and identified by whatever
technology is being used to make the connection or doing the binding. A
Primary Key is an easy way to be sure that this requirement will not
jump up and "gotcha" at some inopportune time.
All my tables have autonumber primary keys, or their equivalent.
I would not make a unique index on employer pk-employee pk, but I might
add a timestamp kind of column. With such a column we would have a
record that John Doe worked for Apple from whenever to a certain date,
and then began to work for Hewlett Packard.
yes, pkId is such a column....
I don't always use a 'autonumber' field as a key, it depends on the
data
in most applications, the employeeId field is used as a foreign key in
other tables (vacations, scheduling, payroll), so deleting it out of
tblEmp means deleting its records from all the other tables too
usually, for tracking purposes, business rules would prevent the
deletion of the data, in that case, I'd add 'start date', 'end date'
fields to the table to track when the employeeId is valid
but if you can delete the record and if you use autonumber keys, and
assuming you have keys 1,2,3,4,5,
if you delete 3, and you add a new record, the list is 1,2,4,5,6
then record '3' is gone until you copy the existing table into a new,
duplicate table (tblEmpNew) and rename tblEmpNew to tblEmp
ken wrote: I assume pkid is just a column with no value other then to have it be an autonumber primary key correct? Anotherwords you recomend having primary keys in all tables? Will it be practical to do this even though I may delete some records from these columns in the future? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: mksql |
last post by:
As an example, I am building an authentication mechanisim that will use
data in the 3 left tables to determine rights to objects in a
destination table, diagrammed below. In this structure, multiple
records in the left tables will point to multiple records in the right
table. Normally, I would approach this problem using junction tables
(LeftID, RightID) to create many-to-many joins.
However, given the structure of each table is nearly...
|
by: Good Man |
last post by:
Hi there
I've designed some web applications that are fairly small scale (thousands
of users) but my MySQL tables are indexed well and they all run quickly and
efficiently.
Today I decided to pick up a book on MySQL to learn more about what I don't
know about it... and the book suggests using "Junction Tables" to record
many to many relationships. I've never used them before (or been aware of
them) and I'm wondering what people's...
|
by: Marcy |
last post by:
I am trying to build a database to keep track of training topics completed by
people in my department. Our department has a set of 37 training topics. There
are 7 job classifications in the department. Each job classification has a group
of required training topics. Some of the training topics are required by more
than one job classification. I have the following tables:
TblJob
JobID
JobDescription
TblTopic
|
by: Kostas |
last post by:
Hi all,
I was wondering what's happening in a typical junction table where the
primary key is the combination of two foreign keys from other tables.
I suppose Access automatically creates a concatenated index on the two
attributes that comprise the primary key.
Should I be indexing each attribute separately as well?
Cheers!
|
by: Riley DeWiley |
last post by:
I have an application with two tables, A and B. Each has an autonumber
unique ID field, plus other data.
I have a junction table, AB, containing fields AID, BID, and Count (a
number).
AB has a combined unique key comprising AID and BID, that is, any
combination of the two must be unique, even though any given AID or BID may
exist in many records.
| |
by: LurfysMa |
last post by:
I am working on an electronic flashcard program. Most of the subjects
are simple lists of questions and answers. Those seem to be working.
Some of the "subjects" have "categories" of questions.
One example is a kind of trivia subject. There are several
"categories" such as "geography", "history", etc., like in the Trivial
Pursuit game. Each card has a question in each category.
|
by: que576 |
last post by:
I have created a junction table so that I can relate data from 2 other tables.
Table 1 - Category Table (has the following fields with data)
CatID (primary key)
CategoryName
CategoryStatus
Table 2 - Names Table (has the following fields with data)
NameID (primary key)
Names
|
by: bg_ie |
last post by:
I'm designing a database with 3 tables called Function, Test and
Scene.
A Function has multiple Tests, but a Test has only one Function. A
many to many relationship exists between Test and Scene therefore I
need a junction table between these two tables - giving 4 tables in
total. The Test table would store a foreign key, the primary key of
the Function table.
There is a problem with design though and that is that Functions and
|
by: dbertanjoli |
last post by:
Hello,
I have a questinarrie webform I use INSERT statement(s) to insert a new record in the User and Questions tables and then (HERE IS MY PROBLEM) I need to update my junction table (QuestionScores - has two fields; questionid and scoreID).
What should be a statement to update my junction table?
This is my code except updates to junction table:
<%
' Declaring variables
Dim tmpFullName, tmpAge, tmpDateDB, tmpMedicalRecord,...
|
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,...
|
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...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |