Solved

Is it bad practice to have many to many relationships in a database.

Posted on 2009-05-13
24
1,779 Views
Last Modified: 2012-08-14
Is it bad practice to have many to many relationships in a database.
0
Comment
Question by:Mr_Shaw
  • 5
  • 4
  • 4
  • +6
24 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 60 total points
ID: 24377310
no. many-to-many relationships are normal.
for example, a student-class relation is like that. 1 student can be in many classes, and 1 class shall many many students.

the design for that would be 3 tables: 1xclass, 1xstudent, 1x student_in_class with at least the 2 foreign key fields...
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 60 total points
ID: 24377328
depends what do you mean by many... :-)

having a relationship in the database is good in terms of data integrity, but it's bad in terms of performance
so it's a trade off you will have to consider
the impact on performance really depends on your design and nature of activity
i wouldn't say one should avoid having relationships in the database, since it's not a wise thing to hope that the application programmer won't forget to code all the necessary checks...
0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 80 total points
ID: 24377330
No. It is required in some cases to model the domain correctly.

Example:

If your system has doctors and clinics in it.

Each doctor can work in many clinics.
Each clinic can employ many doctors.
0
 
LVL 18

Assisted Solution

by:daveslash
daveslash earned 60 total points
ID: 24377344

It kind of depends on how you implement it.

The "classic" implementation of a many-to-many relationship involves using an intersection table.

Therefore, if Table1 and Table2 have a many-to-many relationship, then you'd define Table3 that contains the primary keys of both tables.

That is not bad practice. It's reality. Sometimes, things in the real world are many-to-many, and you need a way to model it.

HTH,
DaveSlash
0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 60 total points
ID: 24377383
Hi Mr Shaw,

That depends entirely on the application.

The most common type of relationship is one-to-many.  Most of the applications that I've dealt with can be built almost exclusively with one-to-many relationships.

But it's not always practical to ignore the need for many-to-many relationships.  Consider a retail store that has 100 suppliers.  An product may be bought from any of 50 or more suppliers, but no two suppliers offer the exact same products.

In that case, a many-to-many relationship is probably in order.  Any supplier can sell you some of your 10,000 products, and any of your products can be bought from several of the suppliers.

While you probably could break the relationships apart to be a series of one-to-many relationships, you would be making the database larger and more complicated that it would need to be.


Good Luck,
Kent
0
 

Author Comment

by:Mr_Shaw
ID: 24377495
I asked this question after reading

http://www.datamodel.org/

goto section titled Isolate Independent Multiple Relationships
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 60 total points
ID: 24377596
Many-to-many relationships reflect business rules.
We can not say that they are bad things and ban them.
They simply exist in the business practice and are unavoidable like earthquakes and floods.

They have a bad feature - it is impossible to embed them in the clasical relational model. To avoid this proble we introduce a special table that stays between the M:N tables and transfforms M:N to two relatioships - M:1 and 1:N that could be represented in the model.

This involves joins, indexes and programming - this is the disadvantage.
0
 
LVL 19

Assisted Solution

by:grant300
grant300 earned 60 total points
ID: 24377676
Having a many-to-many relationship between just two tables is unacceptable and is, in practice, just about impossible to model correctly.
 - Never use multiple fields, e.g. Clinic1, Clinic2, Clinic3 to try and create a many to many relationship
 - Never concatenate multiple values into a single field, e.g. Clinics = "ABC1, DEF2, GRT3" to try and create a many to many relationship
 - Avoid duplicating data to try and "flatten out" the many to many relationships, e.g. Doc1/Clinic1, Doc1/Clinic5, etc.

All of these amateur night techniques cause serious problems and unintended consequences when you actually try to populate and query the data.  SQL is designed to work with sets; not to pull fields apart or have to figure out which column joins to which row.

The only way to effectively handle many-to-many relationships in a relational database is to add a third table, e.g. Doctor_Clinic, that contains the primary keys from both the Doctor and the Clinic tables.

Regards,
Bill
0
 

Author Comment

by:Mr_Shaw
ID: 24377712
ok.
I get the fealing from what you are saying is that one of the rules of the relational model is you are not allow many to many.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 24377791
Hi Mr Shaw,

In this case, there's a world of difference between "need" and "implementation".  The business rule may very well dictate that a many-to-many relationship exists.  Implementing the rule doesn't have to be by a classic many-to-many relationship, though it's not prohibited.  


Kent
0
 
LVL 22

Assisted Solution

by:dportas
dportas earned 60 total points
ID: 24378177
There is nothing bad or "disallowed" about many to many relationships. The question is how to represent them properly in the database.

Mr Shaw, I think you are referring to this article:
http://www.datamodel.org/NormalizationRules.html#four

It is actually talking about Fourth and Fifth Normal Form, which deal with how to represent certain kinds of M:M relationship that might result in data anomalies due to non-key join dependencies.

The diagram titled "Initial business request" shows the conceptual model, which includes M:M relationships. This could be implemented in several ways in the relational model but the purpose of 4NF and 5NF is to analyse how to do it the most correct way.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 24378438
@dportas,

I worked in a shop where a rather bright individual built the skeleton of the first data dictionary in 5th normal form.  (The data dictionary required 41 joins to retrieve a single row.)  That prompted the "only average" in the facility to design a number of other production databases in 5th normal form....  :(

Lesson learned:  5th normal form should be reserved for the classroom.  It's about theory, not practicality.


@Mr Shaw,

As an exercise in understanding M:N relationships, sketch the tables necessary to maintain the product/supplier information for a retail operation.  A single product may be available from 50 suppliers, which suggests a 1:N relationship.  But there may be different product pricing, packaging, shipping, availability, associated with each supplier.  In fact, there may be multiple options for each supplier so that there are hundreds or even thousands of options for buying an item from the list of suppliers.  (The Cartesian affect.)

A classic M:N relationship might be two tables, Products and Suppliers.  But practical application might dictate that there be at least three tables, Products, Suppliers, and Packaging (unit, case, etc.).  Tying the tables together may require other "go-between" tables to generate 1:N relationships instead of the inherent M:N.


Kent
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 22

Expert Comment

by:dportas
ID: 24378659
5th NF is extremely practical and used to good effect by many database architects to solve real problems and build more effective systems. One annecdote does not make an argument.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 24378857
@dportas,

The problem with 5th normal form isn't the people that are capable of understanding it.  It's that people that struggle to write good SQL to query a 3rd normal form database suddenly feel challenged to be an architect so they build a 5th NF database.

There are precious few really good architects out there.  Junior DBAs have no business designing databases, much less 5th NF databases.....

0
 
LVL 22

Expert Comment

by:dportas
ID: 24379160
Exactly. 5NF isn't a problem. Lack of knowledge and skills is a problem. That's why it's important to encourage others to learn, to challenge their assumptions and to sharpen their practical skills. 5NF is an important tool that every data architect should know and apply. To suggest that people shouldn't bother because it may be too hard for them is just encouraging mediocrity and accepting the status quo. No-one wants that do they?
0
 
LVL 19

Expert Comment

by:grant300
ID: 24379226
I have to agree with Kdo (at the risk of taking the question and completely hijacking it...) that 5th NF is mostly an academic exercise.  In practice, a production database implemented in 5NF will require extraordinarily complex SQL and almost invariably suffer from serious performance problems.

The best equipped data modelers are those that understand the theory, e.g. 3rd, 4th, and 5th NF as well as the purpose and rationale behind them, and also understand the realities of implementation and the inner workings of the target database engine(s).  Having that full spectrum of knowledge allows the architect to make reasoned trade-offs between theoretical "purity" and practical implementation.  That balance is necessary to the dual goals of any database system of data integrity and performance.

Regards,
Bill
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24379512
>>That's why it's important to encourage others to learn, to challenge their assumptions and to sharpen their practical skills.

The challenge to learn works both ways. DBAs often get used to being the ones "mentoring" programmers to code a certain way, however, it is also DBAs that need to work as programmers to learn why compromises are needed.

I am a DBA and a programmer, and I hate to write PIVOT SQL to retrieve data just because the tables are fully normalized. A practical solution considers all domains, not just relational domain.

Throw the programmer out of the argument for a second. Replication is another example where the more tables you have, and the more heterogeneous replication systems you work with, you will start to appreciate why de-normalization works in many cases. When deploying systems for IBM, we often ran into problems with our own products, and had to work around complex SQL by de-normalizing to get the crud working, while we waited on the DB2 group to provide patches. In theory, the DB2 replication server should have handled the SQL we threw at it to subset our data by the doctor_id, but by the time we had 5 levels of joins for a particular replication snapshot, the agent was crashing.

Real world vs. theory. Theory is useless if you have no practical implementation to apply it with.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24382373
Pivoting tables is a very rare case in the practice.
So I will not overestimate this need.

But joining 9 up to 99 tables (with outher joins and functions in the WHERE clause)
is very often.
The worst thing is that nobody can control this process.
Because the SQLs are created automatically by tools like Siebel, Hibernate
and nobody allows to change the SQL.
Even the DBA with big amount of bravity changes the SQL,
in the next release of the development the new SQL comes again
in the old edition - the automatic generator doesn't care
what means the DBA
Example here (Siebel):

http://www.experts-exchange.com/Database/Oracle/Q_24391451.html

Markgeer:

You have 23 tables in the "from" clause!  (That's a lot!)  And you have all outer joins!  (Those are slower than standard, inner joins.)  And you have some "!=" conditions and some "or" conditions in the "where" clauses.  (These are very difficult for Oracle to optimize.)

isuhendro

Hi markgeer, your comment is indeed make sense. But option to modify the query is difficult option. The sql is generate from siebel oracle product, after defining high level business logic, that most of them are vanilla. There are some existing indexes already in place.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24384645
schwertner, is your example to illustrate how the DBA has no control nowadays due to frameworks? If so, don't worry, we do Hibernate day to day, and we _always_ have to write custom SQL. Hibernate is not a 100% solution.

Hibernate allows custom SQL (HQL) plus we also bypass it with JdbcTemplate in some cases. Projects that do not do custom SQL will likely have performance problems.

So don't worry, the DBA is still critical.

As to applications like Siebel, the DBA is still critical, because when the system runs poorly, he comes in and tunes the horrible SQL by using better indexes, materialized views, etc.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24385776
My example shows how hard is to tune prepared SQLs.
Because firstly you have to understand what happens.

Also in many shops it is forbiden to use custom SQL (due the low level of development culture of the management and developers, low level of education and no understanding). Automation is the slogan today ... bad!
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24385808
You also see that the asker of the above question rejected to modify the SQL.
So don't be so entusiastic. Also the usage of HypersonicSQL in development phase causes big problems
when the project is implemented on a real Oracle DB.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24386008
>>You also see that the asker of the above question rejected to modify the SQL.

I think I am in agreement with you, but not sure. I contend that too much normalization will cause problems for the average developer. Is that what you are claiming too? I thought I was agreeing with you but maybe we are misunderstanding each other.


>>So don't be so entusiastic.

I will curb my enthusiasm. :P


>>Also the usage of HypersonicSQL in development phase causes big problems

I was discussing Hibernate HQL (Hibernate Query Language), not HSQL (Hypersonic SQL).
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24387460
There is no direct link to what you say in my comments.
We both work in totally different environments.
My environment is bad one, you are happy to work
in the first  country of the world!
0
 

Author Closing Comment

by:Mr_Shaw
ID: 31581103
thanks
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now