Go Back   Access World Forums > Access World > The Watercooler

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-09-2019, 05:39 PM   #1
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 651
Thanks: 79
Thanked 77 Times in 77 Posts
vba_php is on a distinguished road
usage of 1 to 1's and many to many's

how often are 1 to 1 relationships and many to many relationships used? Most of the work I've done in Access has either been using it as a front end for a web database or building CRM's from scratch. I can't remember many cases where I used 1 to 1 relationships but I think there have been a few. And I have never used a many to many relationship. Most of my CRM applications have been built entirely on 1 to many relationships.

Anybody else got any thots on this?

vba_php is offline   Reply With Quote
Old 11-09-2019, 06:06 PM   #2
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,431
Thanks: 11
Thanked 2,286 Times in 2,238 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: usage of 1 to 1's and many to many's

Technically you are correct. However, a many-many relationship is achieved via 2 1-many relationships (aka junction table). Think students and courses, that is a many-many relationship, but achieved by 2 1-many relationships thru StudentCourses:

Students 1-many StudentCourses
Courses 1-many StudentCourses

That's how a many-many is achieved and quite common.

1-1 relationships really shouldn't exist--you should accommodate the data in just one table--you wouldn't store first name in one table and last name in another even though they are in a 1-1 relationship. Instead you have 2 fields in 1 table for that data.
plog is offline   Reply With Quote
Old 11-09-2019, 06:13 PM   #3
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 651
Thanks: 79
Thanked 77 Times in 77 Posts
vba_php is on a distinguished road
Re: usage of 1 to 1's and many to many's

i've heard of the concept of a junction table but i've never used one.

vba_php is offline   Reply With Quote
Old 11-09-2019, 06:28 PM   #4
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,628
Thanks: 58
Thanked 1,456 Times in 1,437 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: usage of 1 to 1's and many to many's

Quote:
Originally Posted by vba_php View Post
i've heard of the concept of a junction table but i've never used one.
How familiar are you with the concept of Normalization? Just curious...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-09-2019, 06:30 PM   #5
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,628
Thanks: 58
Thanked 1,456 Times in 1,437 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: usage of 1 to 1's and many to many's

One use of a 1-to-1 relationship is if you want to split the information between sensitive and non-sensitive data. For example, an employees table could be split into employee data and salary data (including SSN, etc.)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-09-2019, 06:46 PM   #6
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 651
Thanks: 79
Thanked 77 Times in 77 Posts
vba_php is on a distinguished road
Re: usage of 1 to 1's and many to many's

Quote:
Originally Posted by theDBguy View Post
How familiar are you with the concept of Normalization? Just curious...
it's funny you mention this my friend, because yes I am. I know what the 3 levels (or 4 levels?) and what it's all about. However, in my 2 year tenure I've worked for many small businesses and in most of those situations the business owners are always right (even though sometimes I get my way). And of course they don't give 2 shits about normalization, nor do they understand it. So a lot of times, "proper" architecture, when it comes to building a database app, goes by the wayside at least to some degree due to customer demands and complaints. I know there is some degree available of saying NO when it comes to this, but none the less, no app is absolutely perfect and I've never expected it to be.

Coincidentally, the people that I've heard harp the most about the normalization concept is access database developers. Not sure if there's a trend there or not.
vba_php is offline   Reply With Quote
Old 11-09-2019, 06:51 PM   #7
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,628
Thanks: 58
Thanked 1,456 Times in 1,437 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: usage of 1 to 1's and many to many's

Quote:
Originally Posted by vba_php View Post
it's funny you mention this my friend, because yes I am. I know what the 3 levels (or 4 levels?) and what it's all about. However, in my 2 year tenure I've worked for many small businesses and in most of those situations the business owners are always right (even though sometimes I get my way). And of course they don't give 2 shits about normalization, nor do they understand it. So a lot of times, "proper" architecture, when it comes to building a database app, goes by the wayside at least to some degree due to customer demands and complaints. I know there is some degree available of saying NO when it comes to this, but none the less, no app is absolutely perfect and I've never expected it to be.

Coincidentally, the people that I've heard harp the most about the normalization concept is access database developers. Not sure if there's a trend there or not.
Hi. I may only be a lowly Access developer but if the customer requires me to hide sensitive information from unauthorized users, then they can't tell me to not use a 1-to1 relationship. Unless your customers are also database developers, I don't understand how they could require you, the developer, which relationship to use. Can you provide one good example where you violated any of the normalization rules because the customer didn't like/want it? Just curious...

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-09-2019, 07:10 PM   #8
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 651
Thanks: 79
Thanked 77 Times in 77 Posts
vba_php is on a distinguished road
Re: usage of 1 to 1's and many to many's

Quote:
Originally Posted by theDBguy View Post
Can you provide one good example where you violated any of the normalization rules because the customer didn't like/want it? Just curious...
well I'd have to look through all of my project files thru the years, and I don't really wanna do that. But I'm sure there have been a few. but it's not really like they forced me to violate the rules, but rather their constant requests for app behavior changes resulted in "piece meal" bullshit in terms of developing. I've been through this in large corporations as well, so it's not just limited to small businesses.

For instance, I worked for Pearson Education, which is headquartered in London England, and they use a massive application written in Java applets to allow their users to grade student tests. and that application was so god damn piece meal that, here in my city, they had one guy dedicated all day long just to fixing errors that would occur because the code was so sporadic due to "patches" that were written by the various developers over the years.
vba_php is offline   Reply With Quote
Old 11-09-2019, 07:21 PM   #9
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,628
Thanks: 58
Thanked 1,456 Times in 1,437 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: usage of 1 to 1's and many to many's

Quote:
Originally Posted by vba_php View Post
well I'd have to look through all of my project files thru the years, and I don't really wanna do that. But I'm sure there have been a few. but it's not really like they forced me to violate the rules, but rather their constant requests for app behavior changes resulted in "piece meal" bullshit in terms of developing. I've been through this in large corporations as well, so it's not just limited to small businesses.

For instance, I worked for Pearson Education, which is headquartered in London England, and they use a massive application written in Java applets to allow their users to grade student tests. and that application was so god damn piece meal that, here in my city, they had one guy dedicated all day long just to fixing errors that would occur because the code was so sporadic due to "patches" that were written by the various developers over the years.
Okay, I think I understand. Of course, the customer requirement is paramount, but how you deliver it to them should be done with the utmost intent to provide a sound foundation to avoid giving the next guy/gal no choice but to keep applying patches. Just try to imagine you're the next guy... Cheers!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-09-2019, 08:02 PM   #10
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 651
Thanks: 79
Thanked 77 Times in 77 Posts
vba_php is on a distinguished road
Re: usage of 1 to 1's and many to many's

Quote:
Originally Posted by theDBguy View Post
but how you deliver it to them should be done with the utmost intent to provide a sound foundation to avoid giving the next guy/gal no choice but to keep applying patches. Just try to imagine you're the next guy... Cheers!
no question.

on a side note, I have started quite a few threads here in the watercooler. maybe I should shut up now!
vba_php is offline   Reply With Quote
Old 11-09-2019, 08:04 PM   #11
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,628
Thanks: 58
Thanked 1,456 Times in 1,437 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: usage of 1 to 1's and many to many's

Quote:
Originally Posted by vba_php View Post
no question.

on a side note, I have started quite a few threads here in the watercooler. maybe I should shut up now!
Sounds good. I'm going to bed. Good night.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-09-2019, 10:10 PM   #12
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,431
Thanks: 11
Thanked 2,286 Times in 2,238 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: usage of 1 to 1's and many to many's

I had a carpenter add a room onto my house and I forced him to only use 135 degree angles, a miter saw and Allen wrenches. No reason, but I was the client so he did it without even explaining why it was a bad idea.

Whole thing collapsed after 3 months, but he did it just like I wanted so I'd definitely recommend him
plog is offline   Reply With Quote
Old 11-11-2019, 06:23 AM   #13
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,748
Thanks: 93
Thanked 1,724 Times in 1,595 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: usage of 1 to 1's and many to many's

Here's my take on it.

When the customer gives you grief about normalization (or even using the WORD normalization), your correct response is "You hired me to do this because this is something you couldn't do and I could." You can supplement this by explaining that normalized data gives you a better foundation on which to base searches and explorations of relationships between data elements. It also makes it easier for future additions in a way that minimizes physical growth of databases to push back the point of needing to upgrade the data engine you use for SQL operations. So it lengthens the lifetime of the current investment.

Then, you have either an employee relationship or a contract relationship. In either case, you have a fiduciary responsibility to use industry-best practices in what you do for the boss. I.e. the boss didn't hire you to slack off when doing his/her job. Like the GIECO commercial, you didn't hire a doctor who was "O.K." You wanted someone who was GOOD at doctoring. See also: https://www.youtube.com/watch?v=1YT3erQZoq4

Even if you can't explain the "how" to the bosses 'cause it is beyond them, you can explain the "why" of it all. It is done to minimize excessive repetition of data and to isolate relationships to other essential parts of the DB. Normalization distills and focuses on how things fit together, but it does so in an organized and predictable way - which makes subsequent programming costs lower.

This next quote won't mean anything to them, perhaps, but it should be mentioned. Niklaus Wirth, the Swiss computer scientist who devised the PASCAL language (among several others), is often quoted as saying that 80% of all program failures are cause by poor data design (though I can't find the source of the quote at the moment so have to paraphrase). You can look him up with your favorite search engine to get over 30 quotes including some comments on complexity. Wirth is a firm believer in the KISS principle - Keep It Simple, Stupid!

As to the 1-1 relationship: With the Navy we had a few special cases where 1-1 made sense. In each case, we were using the ORACLE protection scheme (which, in Windows would have been called "permissions scheme") to prevent casual viewers from seeing data covered by Privacy Act or HIPAA rules. We also had one other case - where we had 1-1 data regarding a person but it was very large, rarely used, and was likely to cause a record-length issue if retained with the rest of the main record. But if we kept it as a separate table and treated the data as two records most of the time (one main, one supplement), we could manage it reasonably well. Can't tell you what was in it because I knew of its existence but as the Sys Admin rather than the DBA in that particular system, I never had to actually work with it. The only time I ever actually DID deal with it was when I wrote some fancy code to help convert data from ShareBase format to ORACLE format when we were converting. (FYI, ShareBase went out of business but some of their engineers banded together to form SyBase.)
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
vba_php (11-11-2019)
Old 11-11-2019, 07:47 AM   #14
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,628
Thanks: 58
Thanked 1,456 Times in 1,437 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: usage of 1 to 1's and many to many's

I like those commercials!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-11-2019, 07:48 AM   #15
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 651
Thanks: 79
Thanked 77 Times in 77 Posts
vba_php is on a distinguished road
Re: usage of 1 to 1's and many to many's

Quote:
Originally Posted by theDBguy View Post
I like those commercials!
do you know that Warren Buffet owns that company? Along with many others including Dairy Queen.

"So easy a caveman can do it!"

vba_php is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] % usage brownie1989 Queries 0 04-10-2006 05:37 AM
Usage help please... AuroX7 Queries 2 03-30-2006 05:01 AM
Show MAX Usage and Date of Maximum Usage Cosmos75 Queries 1 03-28-2003 01:36 PM
WHERE usage KPMS Queries 0 05-07-2002 02:40 AM




All times are GMT -8. The time now is 03:18 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World