primary key

[Rabbit]

Hello, I'm designing a monster of a database. It will need to allow the user
to collect data in realtime, as quickly as it can be entered. Essentially,
over 8 hours of data collection in a day, each hour is a repeating unit, with
a unique id label (session#, where session = hour). Within that hour, there
are 2 types of data being collected, with subsets of each type. A natural way
to tie all of these separate tables together (in a way that will help later
analysis) would seem to be the session #, but that is causing all kinds of
duplicate id error messages.

How important is it to have a unique primary key? And can a primary key be
tied to more than one table as a foreign key?

Thanks for any help! This website has been a great help so far.

 

[Jeff Boyce]

By definition, a primary key is unique.

To get the most out of the features and functions Access offers, your
database probably needs to be well-normalized. Your statement about tying
"all these separate tables together" makes me wonder about the structure and
relationships of your data.

If you post an example of the "separate tables" and the kind of data you are
storing, the newsgroup may be able to offer alternate approaches.

And on your last question, any "child" table that needs to know its "parent"
would need to have a copy of that parent's Primary key value as a foreign
key (but it could still have its OWN Primary key).

 

[Rabbit]

Thanks for your help. I hope I can explain this so that it makes sense.

Every hour is a session, with a unique number (julian date+hour). Each
session (hour) has two sections of data collection (VR and SR) as well as its
own weather data (wdat) taken, so 2 wdats per hour (taken in the SR1 and VR
sections). The SR section is divided further into 2 types of data taken.
So... my tables are

Date
julian date (jdate)
session PK (linked to SR1, SR2, VR session)
station
observer

SR1
station
observer
session FK
time FK
other data fields... (only one unique data point collected here)

SR2
session FK
Time
Minutes sampled
Observer

SR2data (many data points...)
Autonumbered PK (linked to SR2 session)

VR
Session FK
Time FK
Station
Observer

VR data (many data points...)
Autonumbered PK (linked to VR session)

wdat
time PK
weather fields...

Thinking about it again, it might make more sense to make the date or jdate
from the first table the PK there, linked to Session in the other tables.
Hope that makes sense!

I hope this isn't a stupid question, but if a PK for the 'parent' table
becomes a FK in the 'child' table, is that field automatically populated in
the 'child' table when the 'parent' data is entered?

Thanks in advance for your brain power!

 

[Jeff Boyce]

my comments in-line below ...

Thanks for your help. I hope I can explain this so that it makes sense.

Every hour is a session, with a unique number (julian date+hour). Each
session (hour) has two sections of data collection (VR and SR) as well as
its
own weather data (wdat) taken, so 2 wdats per hour (taken in the SR1 and
VR
sections). The SR section is divided further into 2 types of data taken.
So... my tables are

Date
julian date (jdate)
session PK (linked to SR1, SR2, VR session)
station
observer

If the combination of [julian date] and [hour] is unique, you don't need to
store your [julian date] two times. You could make the combination of
[julian date] and [hour] the primary key. Please note, though, that this
requires you to put TWO fields into the child tables as a (combined) foreign
key. I'd probably use an Autonumber field for a unique row id (and primary
key), as this makes the foreign key-in-child-table process a bit easier.

SR1
station
observer
session FK
time FK
other data fields... (only one unique data point collected here)

SR2
session FK
Time
Minutes sampled
Observer

You said you had two types of SR data (whatever SR means). But the table
structure suggests that the SR1 table is a "child" to the SR2 table (because
it has "time" as a FK, along with session FK). If your two SR tables are
actually just two collections of data about SR, why not put it all in one
table?

SR2data (many data points...)
Autonumbered PK (linked to SR2 session)

Does this mean that SR1 has NO additional data, but SR2 has multiple (child)
data points? If so, you'd need a foreign key in SR2data that points back to
the "parent" SR2 record.

VR
Session FK
Time FK
Station
Observer

VR data (many data points...)
Autonumbered PK (linked to VR session)

Your VR (and VRdata) tables look suspiciously like your SR tables. If SR
and VR are just two different "types", use a single table and add a field
for "type".

wdat
time PK
weather fields...

Thinking about it again, it might make more sense to make the date or
jdate
from the first table the PK there, linked to Session in the other tables.
Hope that makes sense!

I hope this isn't a stupid question, but if a PK for the 'parent' table
becomes a FK in the 'child' table, is that field automatically populated
in
the 'child' table when the 'parent' data is entered?

No, the "child" has no way of knowing unless you tell it who its parent is.
This is handled quite handily in Access by using a main form/subform
construction. The main form holds the parent data, the subform control on
the main form holds the parent-child relationship, and the subform holds
child records.

This is complex enough that it might help if you provided a (real or
imaginary) set of data as an example...

Regards

Jeff Boyce
<Office/Access MVP>

 

[Rabbit]

Unfortunately I can't separate out the [julian date] [hour]. I'm designing
this db for my work where they've used excel tables to collect data for
years. That session id # is entered as one unit (eg 232.01 for session 1 on
jdate 232).

I don't think submitting an example of data would help because it would just
be a bunch of numbers. What we're doing is using radar to study wildlife
movements, so each section (SR1, SR2, VR) is unique. SR1 is one data point
because it's just a count of targets on the screen once each hour (session).
SR2 and VR are each sections where different characteristics are measured of
the targets (eg speed, direction...) so there are many data points per
session. I split those two up into SR2 and SR2 data and VR and VRdata because
at the beginning of those sections, we record data that doesn't change for
the rest of the session, like settings on the radar, etc. So I have the "-
data" tables as subforms of SR2 and VR forms where the many points are
entered (I added FK to those with the corresponding PK in VR, SR2, thank you).

Is it possible to link a primary key from one table to more than 1 table in
a one-to-many relationship? That is, to have the Date table (with jdate,
session etc) with Session as PK linked to SR1, SR2 and VR in one-to-many
relationships? That's what's tripping me up is trying not to duplicate data
but find a way to tie all of these tables together.

Thanks for your help! Sorry if this is so confusing.

my comments in-line below ...

Thanks for your help. I hope I can explain this so that it makes sense.

Every hour is a session, with a unique number (julian date+hour). Each
session (hour) has two sections of data collection (VR and SR) as well as
its
own weather data (wdat) taken, so 2 wdats per hour (taken in the SR1 and
VR
sections). The SR section is divided further into 2 types of data taken.
So... my tables are

Date
julian date (jdate)
session PK (linked to SR1, SR2, VR session)
station
observer

If the combination of [julian date] and [hour] is unique, you don't need to
store your [julian date] two times. You could make the combination of
[julian date] and [hour] the primary key. Please note, though, that this
requires you to put TWO fields into the child tables as a (combined) foreign
key. I'd probably use an Autonumber field for a unique row id (and primary
key), as this makes the foreign key-in-child-table process a bit easier.

SR1
station
observer
session FK
time FK
other data fields... (only one unique data point collected here)

SR2
session FK
Time
Minutes sampled
Observer

You said you had two types of SR data (whatever SR means). But the table
structure suggests that the SR1 table is a "child" to the SR2 table (because
it has "time" as a FK, along with session FK). If your two SR tables are
actually just two collections of data about SR, why not put it all in one
table?

SR2data (many data points...)
Autonumbered PK (linked to SR2 session)

Does this mean that SR1 has NO additional data, but SR2 has multiple (child)
data points? If so, you'd need a foreign key in SR2data that points back to
the "parent" SR2 record.

VR
Session FK
Time FK
Station
Observer

VR data (many data points...)
Autonumbered PK (linked to VR session)

Your VR (and VRdata) tables look suspiciously like your SR tables. If SR
and VR are just two different "types", use a single table and add a field
for "type".

wdat
time PK
weather fields...

Thinking about it again, it might make more sense to make the date or
jdate
from the first table the PK there, linked to Session in the other tables.
Hope that makes sense!

I hope this isn't a stupid question, but if a PK for the 'parent' table
becomes a FK in the 'child' table, is that field automatically populated
in
the 'child' table when the 'parent' data is entered?

No, the "child" has no way of knowing unless you tell it who its parent is.
This is handled quite handily in Access by using a main form/subform
construction. The main form holds the parent data, the subform control on
the main form holds the parent-child relationship, and the subform holds
child records.

This is complex enough that it might help if you provided a (real or
imaginary) set of data as an example...

Regards

Jeff Boyce
<Office/Access MVP>

 

[Jeff Boyce]

Having one "parent" table linked one-to-many with multiple "child" tables is
quite common. After you have the tables designed, with foreign keys in the
child tables, open the Relationships window. Drag the (primary) key field
from the "one" table to one of the "many" table's foreign key field.

Repeat as needed.

--
Regards

Jeff Boyce
<Office/Access MVP>

Unfortunately I can't separate out the [julian date] [hour]. I'm designing
this db for my work where they've used excel tables to collect data for
years. That session id # is entered as one unit (eg 232.01 for session 1 on
jdate 232).

I don't think submitting an example of data would help because it would just
be a bunch of numbers. What we're doing is using radar to study wildlife
movements, so each section (SR1, SR2, VR) is unique. SR1 is one data point
because it's just a count of targets on the screen once each hour (session).
SR2 and VR are each sections where different characteristics are measured of
the targets (eg speed, direction...) so there are many data points per
session. I split those two up into SR2 and SR2 data and VR and VRdata because
at the beginning of those sections, we record data that doesn't change for
the rest of the session, like settings on the radar, etc. So I have the "-
data" tables as subforms of SR2 and VR forms where the many points are
entered (I added FK to those with the corresponding PK in VR, SR2, thank you).

Is it possible to link a primary key from one table to more than 1 table in
a one-to-many relationship? That is, to have the Date table (with jdate,
session etc) with Session as PK linked to SR1, SR2 and VR in one-to-many
relationships? That's what's tripping me up is trying not to duplicate data
but find a way to tie all of these tables together.

Thanks for your help! Sorry if this is so confusing.

my comments in-line below ...

Thanks for your help. I hope I can explain this so that it makes sense.

Every hour is a session, with a unique number (julian date+hour). Each
session (hour) has two sections of data collection (VR and SR) as well as
its
own weather data (wdat) taken, so 2 wdats per hour (taken in the SR1 and
VR
sections). The SR section is divided further into 2 types of data taken.
So... my tables are

Date
julian date (jdate)
session PK (linked to SR1, SR2, VR session)
station
observer

If the combination of [julian date] and [hour] is unique, you don't need to
store your [julian date] two times. You could make the combination of
[julian date] and [hour] the primary key. Please note, though, that this
requires you to put TWO fields into the child tables as a (combined) foreign
key. I'd probably use an Autonumber field for a unique row id (and primary
key), as this makes the foreign key-in-child-table process a bit easier.

SR1
station
observer
session FK
time FK
other data fields... (only one unique data point collected here)

SR2
session FK
Time
Minutes sampled
Observer

You said you had two types of SR data (whatever SR means). But the table
structure suggests that the SR1 table is a "child" to the SR2 table (because
it has "time" as a FK, along with session FK). If your two SR tables are
actually just two collections of data about SR, why not put it all in one
table?

SR2data (many data points...)
Autonumbered PK (linked to SR2 session)

Does this mean that SR1 has NO additional data, but SR2 has multiple (child)
data points? If so, you'd need a foreign key in SR2data that points back to
the "parent" SR2 record.

VR
Session FK
Time FK
Station
Observer

VR data (many data points...)
Autonumbered PK (linked to VR session)

Your VR (and VRdata) tables look suspiciously like your SR tables. If SR
and VR are just two different "types", use a single table and add a field
for "type".

wdat
time PK
weather fields...

Thinking about it again, it might make more sense to make the date or
jdate
from the first table the PK there, linked to Session in the other tables.
Hope that makes sense!

I hope this isn't a stupid question, but if a PK for the 'parent' table
becomes a FK in the 'child' table, is that field automatically populated
in
the 'child' table when the 'parent' data is entered?

No, the "child" has no way of knowing unless you tell it who its parent is.
This is handled quite handily in Access by using a main form/subform
construction. The main form holds the parent data, the subform control on
the main form holds the parent-child relationship, and the subform holds
child records.

This is complex enough that it might help if you provided a (real or
imaginary) set of data as an example...

Regards

Jeff Boyce
<Office/Access MVP>

 

[Rabbit]

Thank you for your help! Problem solved (I hope!). I think what was tripping
me up was that an Index kept being set in the tables, maybe from me editing
and playing with different PK's? Once I realized that, it was much easier to
get relationships working.

Having one "parent" table linked one-to-many with multiple "child" tables is
quite common. After you have the tables designed, with foreign keys in the
child tables, open the Relationships window. Drag the (primary) key field
from the "one" table to one of the "many" table's foreign key field.

Repeat as needed.

--
Regards

Jeff Boyce
<Office/Access MVP>

Unfortunately I can't separate out the [julian date] [hour]. I'm designing
this db for my work where they've used excel tables to collect data for
years. That session id # is entered as one unit (eg 232.01 for session 1 on
jdate 232).

I don't think submitting an example of data would help because it would just
be a bunch of numbers. What we're doing is using radar to study wildlife
movements, so each section (SR1, SR2, VR) is unique. SR1 is one data point
because it's just a count of targets on the screen once each hour (session).
SR2 and VR are each sections where different characteristics are measured of
the targets (eg speed, direction...) so there are many data points per
session. I split those two up into SR2 and SR2 data and VR and VRdata because
at the beginning of those sections, we record data that doesn't change for
the rest of the session, like settings on the radar, etc. So I have the "-
data" tables as subforms of SR2 and VR forms where the many points are
entered (I added FK to those with the corresponding PK in VR, SR2, thank you).

Is it possible to link a primary key from one table to more than 1 table in
a one-to-many relationship? That is, to have the Date table (with jdate,
session etc) with Session as PK linked to SR1, SR2 and VR in one-to-many
relationships? That's what's tripping me up is trying not to duplicate data
but find a way to tie all of these tables together.

Thanks for your help! Sorry if this is so confusing.

my comments in-line below ...

Thanks for your help. I hope I can explain this so that it makes sense.

Every hour is a session, with a unique number (julian date+hour). Each
session (hour) has two sections of data collection (VR and SR) as well as
its
own weather data (wdat) taken, so 2 wdats per hour (taken in the SR1 and
VR
sections). The SR section is divided further into 2 types of data taken.
So... my tables are

Date
julian date (jdate)
session PK (linked to SR1, SR2, VR session)
station
observer

If the combination of [julian date] and [hour] is unique, you don't need to
store your [julian date] two times. You could make the combination of
[julian date] and [hour] the primary key. Please note, though, that this
requires you to put TWO fields into the child tables as a (combined) foreign
key. I'd probably use an Autonumber field for a unique row id (and primary
key), as this makes the foreign key-in-child-table process a bit easier.


SR1
station
observer
session FK
time FK
other data fields... (only one unique data point collected here)

SR2
session FK
Time
Minutes sampled
Observer

You said you had two types of SR data (whatever SR means). But the table
structure suggests that the SR1 table is a "child" to the SR2 table (because
it has "time" as a FK, along with session FK). If your two SR tables are
actually just two collections of data about SR, why not put it all in one
table?


SR2data (many data points...)
Autonumbered PK (linked to SR2 session)


Does this mean that SR1 has NO additional data, but SR2 has multiple (child)
data points? If so, you'd need a foreign key in SR2data that points back to
the "parent" SR2 record.

VR
Session FK
Time FK
Station
Observer

VR data (many data points...)
Autonumbered PK (linked to VR session)


Your VR (and VRdata) tables look suspiciously like your SR tables. If SR
and VR are just two different "types", use a single table and add a field
for "type".

wdat
time PK
weather fields...

Thinking about it again, it might make more sense to make the date or
jdate
from the first table the PK there, linked to Session in the other tables.
Hope that makes sense!

I hope this isn't a stupid question, but if a PK for the 'parent' table
becomes a FK in the 'child' table, is that field automatically populated
in
the 'child' table when the 'parent' data is entered?

No, the "child" has no way of knowing unless you tell it who its parent is.
This is handled quite handily in Access by using a main form/subform
construction. The main form holds the parent data, the subform control on
the main form holds the parent-child relationship, and the subform holds
child records.

This is complex enough that it might help if you provided a (real or
imaginary) set of data as an example...

Regards

Jeff Boyce
<Office/Access MVP>