Re: Content of Foreign Key
- From: "BruceM" <bamoob@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 26 Oct 2005 07:50:18 -0400
I'm going to jump in here and hope that I don't rile the waters. The
correct order is to create the structure (including relationships), then add
the data. Relational databases start with tables and relationships. Forms
are the user interface.
To stress a point that has already been mentioned, if you are using an
autonumber PK (primary key) in Orders, put the field into the Orders table,
put a corresponding Number field into the Order Details table, create the
relationship between the two fields, then forget about it. Every time you
use a form to create a record for an Order it will have a unique autonumber
PK. Every record you create in the corresponding subform will have that
same number in the related field.
Do what you wish for naming fields, tables, etc., but it helps to be
consistent. I would call an Orders table tblOrders. A query based on that
table would be qryOrders, the form would be frmOrders, and so forth. With a
nod to Albert, who has provided endless valuable advice to myself and others
in this forum, I prefer to stay away from generic field names. I would tend
to use OrderID as the name of the PK field in tblOrders, and would also use
OrderID as the name of the related field in tblOrderDetails.
tblOrderDetails may have its own ID field, which needs to be a different
name than any other field in that table. I find it easier to create
relationships between fields with the same name. That's just how my brain
seems to be wired.
Using the naming scheme I have outlined, if the PK for an Order record is
123, every correspondig record in the Order Details will have 123 in the
OrderID field. Every record in OrderDetails with 123 in the OrderID (child)
field will be related to one and only one record in tblOrders. One common
example of how this works is with payroll. If you have an EmployeeID number
where you work, every payroll record for you will contain that number.
Those records will be associated with you and only you, because nobody else
has that same EmployeeID number. If your name changes, the old records will
be carried forward since they are associated with an unchanging number.
A word on junction tables, which are used in specialized situations. If you
are keeping track of students and courses, each student will take many
courses, and each course will be attended by many students. Therefore the
relationship between Students and Courses is many-to-many. You cannot
create a many-to-many relationship directly between two tables, so a third
table is needed to resolve the relationship. This third table (I will call
it tblEnrollment) is related to both the Students and Courses tables. I
don't think you need to concern yourself with that in your situation. Each
OrderDetail is associated with only one Order. With Parts you may want to
store the Part information in the OrderDetails table (the price, at least)
rather than linking to the Parts record, so there is probably no
many-to-many relationship. I mention this because junction table is a term
in common usage, and not to argue with Albert's point that the term can
become confusing. It is, after all, just another table.
"ffisher" <ffisher.1xi65p@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ffisher.1xi65p@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>
> Thanks to Albert and John,
> Reading through both of your replies it seems the approach is to create
> a form to fill in the information from the PK into the FK.
>
> First I will add data to the tables then create my relationships and
> then create the form.
> I assume this is the correct order of working.
> Thanks.
> ffisher
>
>
> --
> ffisher
.
- Follow-Ups:
- Re: Content of Foreign Key
- From: ffisher
- Re: Content of Foreign Key
- References:
- Content of Foreign Key
- From: ffisher
- Re: Content of Foreign Key
- From: ffisher
- Content of Foreign Key
- Prev by Date: Re: query object
- Next by Date: Re: "Updating" a report to new query changes and leaving out things like sorting in report?
- Previous by thread: Re: Content of Foreign Key
- Next by thread: Re: Content of Foreign Key
- Index(es):
Relevant Pages
|
