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

I asked this question after reading

http://www.datamodel.org/

goto section titled Isolate Independent Multiple Relationships

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.

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

@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

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.

@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.....

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?

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

>>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.

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):

https://www.experts-exchange.com/questions/24391451/Oracle-index-or-not-indexed-after-review-db-stats.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.

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.

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!

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.

>>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).

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!

thanks