Primary and Foreign Keys

[vandy05]

I am trying to work on a database and now I am getting completely confused and need some help.
Can someone explain to me how primary and foreign keys work?

Here is a basic structure of what I am planning.

Table 1
Site ID
Site Name
Site Address (broken up of course)

Table 2
Personnel ID
Site ID
Name
Phone
Email

Table 3
Project ID
Site ID
Project #
Budget

Table 4
Contact ID
Personnel ID*
Employee ID*
Site ID *
Date
Comments
*only think i need these because i will be making a report that will be using data from all of these tables.

Table 5
Employee ID
Employee Name

Table 6
Payment ID
Site ID
Amount

Table 7
Validation ID
Site ID
Results

So my question is - in order for me to fill out my forms, i will have to remember all the ID numbers? and cannot auto-fill any fields?

I just don't get it. Please help. I have been working on this DB for 4 weeks now and I keep getting so close and then having to start over.

 

[Banana]

It does look to be set up correctly from a quick glance.

While database does store the data in IDs, it doesn't mean you should be reading/writing IDs. Normally we would use a combobox or listbox to select a value; it'd be actually bound to the ID column, but the combobox or listbox would display the column that has human-readable and meaningful value. Thus, Access should be managing the ID behind the scenes while your users select data based on actual data.

You are using forms, right?

 

[vandy05]

Yes I will be using forms. That's what I thought - My issue now is that there could be 1000s of entries so the combo boxes would be insanely long!

 

[boblarson]

Yes I will be using forms. That's what I thought - My issue now is that there could be 1000s of entries so the combo boxes would be insanely long!

If you have the Auto Expand property of the combo set to YES then when your users type in the combo box it will move to the item that matches. But, if your combo is going to have over 65,000 rows (and possibly even fewer) it is good to come up with a way to limit what shows up there.

For example, if I had a combo where I was going to be selecting names, I could have another (unbound) combo which allowed me to select the first letter of the last name and then the other combo with the names would be limited to only those.

 

[vandy05]

okay i understand the auto expand and thats great....but how does it help with something like all the ID's i will be using since they are all numbers....so many numbers!

 

[Banana]

The thing is it can depend on what your requirement are, but here's few possible options:

1) You can have it off initially and use OnChange event to turn it back on only after n character has been entered, filtering the list.

2) You can filter the list using query to show only "active", "current" or whatever IDs you need to see

3) You can use cascading comboboxes to select a group of ID, then select ID from the filtered list for that group...

Hope that give you some ideas..

 

[boblarson]

okay i understand the auto expand and thats great....but how does it help with something like all the ID's i will be using since they are all numbers....so many numbers!

A combo box is where you let it have the ID number behind the scenes and you have it set up so that the TEXT displays for the user, but when they select the item it stores the ID in the bound field, but continues to show the data selected as text.

 

[vandy05]

A combo box is where you let it have the ID number behind the scenes and you have it set up so that the TEXT displays for the user, but when they select the item it stores the ID in the bound field, but continues to show the data selected as text.

i tried that and it didn't work for me...maybe i am doing it wrong?

 

[vandy05]

The thing is it can depend on what your requirement are, but here's few possible options:

1) You can have it off initially and use OnChange event to turn it back on only after n character has been entered, filtering the list.

2) You can filter the list using query to show only "active", "current" or whatever IDs you need to see

3) You can use cascading comboboxes to select a group of ID, then select ID from the filtered list for that group...

Hope that give you some ideas..

could you explain a little more about #1 please

 

[Banana]

First, I want to verify if the ID you're talking is actually a key, and not a identifier for data you're working with. If this is truly the case, we'll have to work with this because usually we don't have our users work with keys. It should be all behind the scene.

i tried that and it didn't work for me...maybe i am doing it wrong?

The combobox should have those following properties set:

On Format tab:

ColumnCount: 2
ColumnWidth: 0;

On Data tab:

Recordsource: ID (representing the foreign key for the table the form is bound to)
Rowsource: SELECT ID, ActualData FROM YourTable; (Adjust the naming as necessary)
Bound Column: 1 (should be default but wanted to be sure)


See if this help?

 

[vandy05]

I am a little confused on what you are talking about the ID being an actual key not an identifier....
the ID is an autonumber - the primary key
so doesn't that mean it is used as an identifier for the data in the rest of the row in the table?

First, I want to verify if the ID you're talking is actually a key, and not a identifier for data you're working with. If this is truly the case, we'll have to work with this because usually we don't have our users work with keys. It should be all behind the scene.



The combobox should have those following properties set:

On Format tab:

ColumnCount: 2
ColumnWidth: 0;

On Data tab:

Recordsource: ID (representing the foreign key for the table the form is bound to)
Rowsource: SELECT ID, ActualData FROM YourTable; (Adjust the naming as necessary)
Bound Column: 1 (should be default but wanted to be sure)


See if this help?

 

[gemma-the-husky]

Table 1
Site ID
Site Name
Site Address (broken up of course)

start at the beginning

take this table - if you want to see details for a site you have a few ways of doing it.

either
a) enter the siteid directly
b) enter the site name and let access show you things that match

or even
c) enter a rep's name. and see a list of all the sites he deals with, then pick the one you want.

---------
in all these cases, you are trynig to get at the siteid - but you dont have to know or even care what the siteid actually is,

HOWEVER - when you use the expression siteid, in your paticular case , do you really mean a site ref you use in your company. If so then it would be bettter for this NOT to be the same as siteid

instead have this

Table 1
[COLOR="Red"]Site ID (an autonumber field)[/COLOR]
[COLOR="Blue"]Site Ref (YOUR COMPANIES SITE REF)[/COLOR]
Site Name
Site Address (broken up of course)

so now you can find the siteid, using any method you want (eg enter the site ref, pick from a list of sites, post code match etc

but the SITEID is just a meaningless number that is only there to provide a link within your database. You dont even have to ever see it - it just works behind the scenes

eg

Table 2
Personnel ID
Site ID
Name
Phone
Email

in your table2 the siteid should be the number that corresponds to the actual site, and you are using it to identify the employees working on that site. But you picck the site by whatever means, and Access then AUTOMATICALLY selects just those employees attached to that particular siteid - which is why you dont need to remember or care what the numbers actually are (other than during development, when it can be useful to know whats going on, and to see that things are working correctly)

 

[vandy05]

I am starting the DB again with this information but I have a question.

Table 1 - Site ID (Primary Key)
Table 2 - Personnel ID (Primary Key), Site ID (Foreign Key)
Table 3 - Project ID (Primary Key) , Site ID (Foreign Key)
Table 4 - Contact ID (Primary Key)

The Contact form and report that I need to create needs information from table 1, 2 and 3. I don’t have any of the information being stored in the table but I will need it to show up on the forms to ensure proper data entry and on the reports as well.
So my question is….do I need to call the site ID, personnel ID and Project ID in table 4 or since site ID is included as a foreign key in tables 2 and 3 it is not necessary?

 

[gemma-the-husky]

The Contact form and report that I need to create needs information from table 1, 2 and 3

ah, but does it really?

it depends what you mean by this, but often when you think you need multiple tables, you really still have a normalisation/table design issue. So what information are you trying to store in the contact table.

a slightly different issue in your above example
a) what purpose does the project serve to distinguish it from the site.
b) if you just link the employee to a site, then an employee can only work on one site - but presumably you have some employees who work on more than 1 site.

 

[jdraw]

I am starting the DB again with this information but I have a question.

Table 1 - Site ID (Primary Key)
Table 2 - Personnel ID (Primary Key), Site ID (Foreign Key)
Table 3 - Project ID (Primary Key) , Site ID (Foreign Key)
Table 4 - Contact ID (Primary Key)

The Contact form and report that I need to create needs information from table 1, 2 and 3. I don’t have any of the information being stored in the table but I will need it to show up on the forms to ensure proper data entry and on the reports as well.
So my question is….do I need to call the site ID, personnel ID and Project ID in table 4 or since site ID is included as a foreign key in tables 2 and 3 it is not necessary?

I realize there has been a lot of discussion on this thread, but I don't see any info on what this database is trying to support.
What is the application?
Before you get down to which button to click on which form, you may wish to step back and draw a picture of the database in non-technical english terms.

You may also want to make a list of the questions/business situations the database is intended to support/address. If the proposed drawing/structure can not answer the question, then adjust the drawing accordingly.

Too often there is a jump into the technical details without a picture and understanding of what it is you're trying to do.

This is not meant as criticism. I have seen this many times.

Draw the picture, start adding fields/columns to some entities/tables.

Mock up a report request. Make sure you have the fields that can support the request. We called this "stump the model". Adjust the picture until it supports some of the reports/forms and processes you're trying to assist/solve/support.

The more of this you do, the less time you'll spend designing as you program.
The more you do with the drawings/mock ups, the better you'll understand your business.

There are some sample database models athttp://www.databaseanswers.org/data_models/index.htm

that may assist you. I am not connected in any way to that site. It may give you some ideas of how tables can/should be linked. Of course, your situation may not fit any of this samples.

Good luck.

 

[vandy05]

let me explain this a little more...
each site has more than one employee thus there is a personnel table...each site can participate in more than one project - thus the project table....
now the contacts table - there is a required report that needs to be printed which includes the site name, person contacted, date contacted, project information, pi information, what was actually said during the contact. everything so this table needs to exist and needs to pull info from all these other tables.
the contact table is storing the the actual conversation information - ie date contacted, and what was said...

now on to your questions
a - each site can participate in more than one project - thus the projects table is necessary
b - the personnel are linked to the site not my company - thus the employees are linked to the site but since the project is also linked to the site and and change the employees can work with more than one project

 

[vandy05]

okay here you go look at this excel breakdown that i have made - maybe this will explain what i am trying to do.
i dont know how else to explain it and its just getting frustrating.

 

[vandy05]

forgot to mention. items in () are needed on reports that are being printed.
also they need to show up on the forms so that the user knows they are entering the correct information

 

[gemma-the-husky]

ok i see

well as far as the contact table goes you have details such as

contact date
contact content
contact employee
contact site

the items in red being details linking them to your other tables

now there are several ways of doing this, and it possibly depends which is the central table in your database (the site possibly) - but you dont need to link all the tables to ENTER the data - all you need to do is consider your data from the point of view of the CONTACT table (for this purpose)

so one way is to have a contact form, showing the date, what was said, follow up date etc - and have dropdowns (combo boxes) to select certain or all of

a) the project and
b) the site from the sites included in that project and
c) the employee on that site

you could shorten this to not needing all three of these, depending on yuor project/site/employees link together - hence the previous questions

but eg, since the employee depends ON the site, you may have to pick the site first, then the employee for that site - look at cascading combo boxes for this sort of functionality - the point is still that your form is only bound to the contacts table, and the links to the other tables are offered (only) via the combo boxes
---------
alternatively you could start from the site form, and have a button that says "Enter Contact" (or even have a subform that shows all the contact history for that site) - now you don't need to select the site because you are already on the site - but you do still need (a combo box drop down probably) to select the employee)

either of these ideas will work fine - you could even have both - its just deciding on the lok and feel of the interface

-----------
now when it comes to a report, thats different - now you need a query that does draw all your tables together, from which you select the certain data elements you want to see on your report. - but a report is not generally interactive, so it behaves differently

 

[vandy05]

so let me see if i get what you are saying. i dont need to put site id, personnel id, and employee id in the contacts table?
how would that work since i have to know who is linked to what. there are going to be 1000s of sites and 1000s of personnel....