2 Replies Latest reply on Jun 12, 2012 2:51 AM by revmk

# Too many relationships

Hello all,

Can anyone tell me the following;

1) Is there a maximum number of relationships each table can have?

2) Can the number of relationships cause poor performance?

Thanks

• ###### 1. Re: Too many relationships

Number of tables per file: 1 million.

Number of relationships per file: Limited only by disk space or maximum file size.

Number of fields/columns per record: 256 million total fields over life time of file.

a) With regards to your question, maximum number of relationships per table: This can't be answered without looking at a second, different table. Assume we have table 1 with n fields and table 2 with m fields, the n and m fields all being of the same field type (e.g. all numbers or all text), then one could create in principle (although this makes no sense) a maximum of n times m relationships or TO (table occurence) pairs. You can now continue with a third, fourth, ... 1 millionth table and will see that the number of combinations will explode quickly (e.g. for a third table with o fields ==> n times m + n times o + m times o possible combinations).

The specifications state the maximum number of tables and fields/table, but not the maximum number of TOs that can be specified in a relationship graph. Anyway, I would like to see that relationship graph that would approach only to a fraction (say 1%, which is 10000 tables) of the table number limit. Probably the rendering engine would fail on such beasts.

See the "Solutions are complex, right?" thread for some nice relationship graphs.

b) Generally it is known that relationships involving (a combination of) inequality operators (>, <, >=, <=) may affect performance, so use those sparingly. No problems known for equality and cross-operator relationships if key fields are indexed and don't involve complex calculations.

• ###### 2. Re: Too many relationships

Hi Martin,

Thanks for the superb reply.  My mind is at ease after looking at that thread.  Some of those solutions are huge!  I was worried that 5 equal relationships were going to start causing issues, looks like I have a way to go yet.

Thanks again.