Basic performance and good design question abt forms/subforms queries

[Veli Izzet]

Hi all,

For better performance and neat design:

Table1
Table1ID
Table1Whatever

Table2
Table2ID
Table2Whatever

Table3 (For relationship)
Table1ID
Table2ID

For forms/subforms or queries in general,

1-Do we have to include into the query ALL Table1ID.Table1,
Table2ID.Table2, TableID.Table3 and Table2ID.Table3?

2- Is it better to include ONLY the relevant fields in the query, or put
every field just in case?

Thanks for answers.

 

[Albert D.Kallal]

I assume that table1 is your "main" table, or your master table.

I assume that table3 is your table that you relate to table1.

So, there is really no relation from table1 to table2...is there?

But, to answer you question, you only need to include the fields in table1
for the form that you build. While you don't need to place the pk field on
the screen, you do need to include the PK it in the query for that main
form.

As a rule, including a few extra fields in the query is not going to make
any difference in performance.

As far as I can tell in your example, you only need table1 for the main
form, and table3 for the sub-from.

So, no, you don't need any quires with relation joins. However, how you
display values from table2 in the sub-form may change my above suggestion.

For example, if you are not using a combo box in the sub-form table3 to set
the table2 id, then you could consider basing that sub-form on a join of
table2--->3. Then, when you type in the table2 id value, then a column would
show other fields from table3. However, as you have it, we don't know what
kinds of values you need to display from table3. So, really, you do not have
to join the extra tables as you laid things out. And, in fact, your sub-form
query based on table3 does NOT need to join in, or include any table2
fields.

So, form a performance point of view, since the main form is going to be
based on table1, then that main form DOES NOT need to include any other
fields from the other two tables (so, no, the main form does not need a
query that includes any other fields, and it would be a waste to do so).

Note that the sub-form (table3) again does not need any fields from any
other tables, and again DOES NOT need to include any other fields from the
other two tables (again, it would be a waste to do so - unless you *need* to
display some other fields from table2 in the sub-form)..

 

[Veli Izzet]

Table1 is the main table
Table2 is the sub table

Table3 is the table thatr relates Table1&Table2

 

[Albert D.Kallal]

Table1 is the main table
Table2 is the sub table

Table3 is the table thatr relates Table1&Table2

Yes, ok..then my advice is correct.

I really don't like using the term table3 relates table1 and table2. The
often used term is a junction table. (what on earth is that?).

When I explain relation stuff to people, I don't use the term junction
table, and I NEVER use the concept that table3 relates two tables. (it just
confusing the heck out of everyone).

So, my advice I mentioned to you still remains.

However, lets take up a quick example of what I mean here, and give some
meanings to those tables.

So, I going to use table1 - tblPeople (so, we can change/give each table a
meaning).

We want to make a database of our friends, and we want to track their
favorite colors. So, the first thing we need is a table of our friends

table1 - tblPeople our "main master" table of people
this table would have fields like:
id - Pk (primary key...likey a autonumber id),
FirstName
LastName
etc.

The next thing we need is a table to list out, or keep track of their
favorite colors. This table will be RELATED to table1

table3 - tblFavorateColors
Table1_ID - this realates to table1
Color - our favorite color

table2 - tblColors
Color (pk)

At this point, you might start to argue that table3 is related to table2,
but really, it is not. If you add, or delete records in table3
(tblFavorateColors), then we do not have to add, nor delete records to
table2 (tblColors). So, in effect, table2 is just a list of colors in a
table sitting there. There is NO NEED to build a relation from table3 to
table2.

So, we could build a form based on table1 (tablePeople), and the sub-form
would be the list of their favorite colors.

John smith

--->sub form
red
green


So, we can see that john smith has two favorite colors. At no point did we
join in, or use table2 here. However, often the PK would not be something
like a color, and thus a user cannot normally "remember" those values. Thus,
you OFTEN use a combo box that stores the id, but displays some text.

To be fair, you can (and should) enforce referential integrity from table3
(list of colors) to the lookup table of colors (tblcolors). Why? Well, you
might want to delete a un-used color, and if you got RI, then you cannot
delete a record in tblColors (table2) if any of them are being used in
table3.

So, to state that the table3 is used to relate the two tables is not the
best way of explain things. Table3 is related to table1. Each new record
you add to table3 is related to table1. Table3 is NOT related to table2. It
is the other way around!! So, table2 is related to table3!!

If you look at the relationships window, you will get:

table1----->table3--------->table2

Looking at the above, I don't see table3 relating the two tables, the arrows
are ONE direction.. We might have:

table1----->table3--------->table2------>table4

So, in the above each table only has ONE parent table. This whole concept
goes ONE way.....

You relate ONE table to another. That is ALL you can do, and it keeps the
whole thing very simple.

Each table only has ONE parent table.....

So, you can say you need a junction table..but I think it is MUCH more clear
to simply state that you need a table that lists peoples favorite colors.
And, if you want a list of colors, then make a table called "listofcolors".
So, you start at the top...and keep working you way down, one table at a
time....