09-06-09, 18:27 #1Registered User
- Join Date
- Jul 2008
Unanswered: Why make one to one join? Keep it or not?
For starters, let me do a CMA (cover my... (not Access)) ;
The attached file cannot be reproduced or used for any kind of financial gain without the express legal consent of the book's authors/publisher/and any other entity claiming its legal right to the info (Addison/Wesley ISBN 0321517016).
OK, did I need to do that? I know the majority of the members here would chuckle at needing to use the file, but better safe than sorry I guess.
Here is the file:
Colorado Ski club with kids learning to ski.
Season is Nov. to Mar.
Each season 8 events are held.
$200 per student per season covers all but equipment rental
Equip. rental $50/set/season
A student can rent more than one Equip. rental unit per season yet there is not information about pro rating the rented sets.
OK, now onto the issue in the title (Why make one to one join? Keep it or not?):
The parent who is responsible for the billing may not always live at the same address as the member, the child(ren), so put the parent's name and address in a separate table and join the two tables with the member ID number.
NOTE: children is my sticking point/problem.
Parent data will only be used for billing reasons and does not need to be seen every time the members table is opened.
are joined with a one to one join and in the tblParents the MemberID field is a lookup field:
SELECT [tblMembers].[MemberID], [tblMembers].[LastName] FROM tblMembers ORDER BY [LastName];
What if a parent, the one responsible for billing is at an address different from his/her two three or more kids, children?
So, I studied the tblParents table and found only one student record, last name Vaden, whose parent's last name is different in parent table, Marlena Bowman. The 23 records were already created with the sample file.
I went to tblMembers and added another student record with last name Vaden and there was a problem since the primary key in the tblMembers is a text field apparently entered manually as an autonumber.
The book gets into issues with the autonumber primary key that do not need to be discussed here as far as I am concerned.
So I had to sort the tblMembers to find the next avail. #, primary key value. I am thinking of course what if there was a hundred or more students...
With those details in mind, once again my questions are:
Why not make the tblParents the ONE table and the tblMembers the many table, classic primary/foreign key join?
If there is some reason for using a one-to-one join that I am not aware of, then why set the primary key in tblMembers as text data type and enter sequential numbers?
Why not set the primary key in both of the one to one tables as a number data type?
If all of you can give me valid reasons as to why a one to one join is best and why setting the numbers as text data is best then I did find the article below on how to readily sort numbers set as text data type in order to find the next available key value.
I found a solution to sorting text based numbers, http://office.microsoft.com/en-us/ac...26521033.aspx.
I thank you all very much in advance. Last year you gave me great advice about why it can make sense not to have a primary key in a table.
09-06-09, 20:12 #2L33t Helpa Munky
- Join Date
- Nov 2007
- Adelaide, South Australia
Personally, I tend to merge tables with 1:1 relationships together.Owner and Manager of
CypherBYTE, Microsoft Access Development Specialists.
Microsoft Access MCP.
And all around nice guy!
"Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
"...teach a man to code and he'll be frustrated for life! " -- georgev
09-06-09, 20:41 #3Registered User
- Join Date
- Jul 2008
Thanks for your reply.
I am just starting to 'walk' with Access and your input is very helpful.
09-06-09, 23:09 #4Moderator
- Join Date
- Dec 2004
- Madison, WI
I'll use one-to-one joins when I need to analyze certain datasets or need to ensure only one record will exist in the relational table (along with establishing the primary key correctly as you stated.)
Sometimes I may even break the normalization rules and put data that's in a relational table into the main table also (ie. query performance when you're working with millions of records) - every joined table in a query takes a hit on performance and having certain "grouping" type fields in the main table makes returning totals on 5 million records much more efficient (providing it's a one-to-one type of relationship.)
Sadly, I've seen developers put fields such as the city, state, zip, etc.. all in separate relational tables (which will cause an application to crawl when you need to return all the names/addresses on large recordsets). Especially when some of the users have slow external connections.
I often try to avoid storing only a number in the main table which corresponds to a number in a "lookup" type table and instead, just store the text value in the main table. It depends on the situation. I frown heavily upon using the lookup tab for a field in the table design since this is mainly for users entering data into the table directly which I never allow (always through a form!) It just makes life miserable for those who inherit the design or need to analyze the data. I've seen far too many situations where developers don't consider that someone else might need to make changes to their application and design their apps cryptic for others to understand.
When I design my tables/forms/etc, I consider things such as: stability/speed of the network, external users and their connection speed, types of totals needed, number of records anticipated, type of application, etc...etc.. Some may argue against this but I'd rather have an application which users WILL use versus one users don't use because it crawls and takes them 20 minutes to enter a record.
Last edited by pkstormy; 09-06-09 at 23:35.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)