I know many to many (junction table) (other table primary key and other table primary key)

So I wondered, not many to many tables, but many to many to many or many to many to many to many to ..

Is it possible to infinity in one table? (Isn't there a problem in theory? Can a lot of overlaps be fatal? How many fields are usually allowed?(in average?))

CodePudding user response：

I've never seen a legitimate case of a "many-to-many-to-many" relationship. It's almost certainly two many-to-many relationships, each one between two of the tables in the set of three.

Each many-to-many relationship should have its own intersection table. If you try to combine them, it's almost guaranteed to be a violation of fourth normal form.

You can see some other answers I've written about problems that occur when you break fourth normal form:

• What are appropriate ways to represent relationships between people in a database table?
• Proper way to model M:N relationship in SQL where parent may be one of many types
• SQL - Two foreign keys that have a dependency between them

But to answer your question more practically, with respect to MySQL there is a finite number of columns you can define in a table. Exactly what that limit is is a complex subject, related to the way MySQL stores metadata, not really a theoretical issue. See this blog for details: https://www.percona.com/blog/2013/04/08/understanding-the-maximum-number-of-columns-in-a-mysql-table/