usage of 1 to 1's and many to many's (1 Viewer)

vba_php

Forum Troll
Local time
Today, 03:04
Joined
Oct 6, 2019
Messages
2,884
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?
 

plog

Banishment Pending
Local time
Today, 03:04
Joined
May 11, 2011
Messages
11,613
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.
 

vba_php

Forum Troll
Local time
Today, 03:04
Joined
Oct 6, 2019
Messages
2,884
i've heard of the concept of a junction table but i've never used one.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:04
Joined
Oct 29, 2018
Messages
21,358
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...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:04
Joined
Oct 29, 2018
Messages
21,358
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.)
 

vba_php

Forum Troll
Local time
Today, 03:04
Joined
Oct 6, 2019
Messages
2,884
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:04
Joined
Oct 29, 2018
Messages
21,358
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...
 

vba_php

Forum Troll
Local time
Today, 03:04
Joined
Oct 6, 2019
Messages
2,884
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:04
Joined
Oct 29, 2018
Messages
21,358
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!
 

vba_php

Forum Troll
Local time
Today, 03:04
Joined
Oct 6, 2019
Messages
2,884
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! :D
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:04
Joined
Oct 29, 2018
Messages
21,358
no question.

on a side note, I have started quite a few threads here in the watercooler. maybe I should shut up now! :D

Sounds good. I'm going to bed. Good night.
 

plog

Banishment Pending
Local time
Today, 03:04
Joined
May 11, 2011
Messages
11,613
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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:04
Joined
Feb 28, 2001
Messages
27,001
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.)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:04
Joined
Oct 29, 2018
Messages
21,358
I like those commercials! :D
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:04
Joined
Oct 29, 2018
Messages
21,358
do you know that Warren Buffet owns that company? Along with many others including Dairy Queen.

"So easy a caveman can do it!"
Nope. I had no idea. Thanks.
 

vba_php

Forum Troll
Local time
Today, 03:04
Joined
Oct 6, 2019
Messages
2,884
Nope. I had no idea. Thanks.
not to carry this on further, but I think it's quite amusing that Buffet's dietary habits are like the worst of anyone in the world. He's a self professed McDonald's junkie and apparently eats the food 4 times a week and drinks plenty of Coke. and he's pushing 90. just goes to show that the doctors and dietitions are not always right. same thing with smoking. I've known plenty of heavy smokers that lived well into their 90s.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:04
Joined
Oct 29, 2018
Messages
21,358
not to carry this on further, but I think it's quite amusing that Buffet's dietary habits are like the worst of anyone in the world. He's a self professed McDonald's junkie and apparently eats the food 4 times a week and drinks plenty of Coke. and he's pushing 90. just goes to show that the doctors and dietitions are not always right. same thing with smoking. I've known plenty of heavy smokers that lived well into their 90s.
Well, he's filthy rich too, so do we know if he didn't have his body replaced with robotic parts? Just kidding...
 

vba_php

Forum Troll
Local time
Today, 03:04
Joined
Oct 6, 2019
Messages
2,884
Well, he's filthy rich too, so do we know if he didn't have his body replaced with robotic parts?
if anyone would have done that, i would have to think it would've been Jobs.
 

deletedT

Guest
Local time
Today, 08:04
Joined
Feb 2, 2019
Messages
1,218
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."

Doc, not always true.
I've asked others to do things for me, just because I've been too busy to do it myself. And despite the fact that I could do it better, I had to ask someone to do it instead of me. Not only in 3D Cad design (my main job) but also in PHP, VBA, NC programming or even Laser Cut manufacturing and a few more. And each time I see they pick an idiot way to do something, I've asked them to change it and do it as I say. Because when the job is done, I'm responsible for it and after care weigh is on my shoulder, not theirs.
 

Users who are viewing this thread

Top Bottom