Can you have too many relationships between your tables?

[tatarik]

I'm building an Access database that is growing bigger, and I might not have considered this aspects when I initially started.

So what is a good method to find out whether you have too many relationship between your tables?

Thanks in advance!

 

[Galaxiom]

Access has a limit of 32 relationships per table less the number of other fields that are indexed.
The right number for the whole database depends on your data structure.

You could post an image of your relationships window.

 

[tatarik]

Thank you Galaxiom.

I was actually concerned with the possibility of having redundant relationships in particular.
Relationships with referential integrity can be constraining when I go further into my database development, so I thought it might be a good idea to get read of the useless relationships.

Here is a screen copy of my tables relationships (attachment: relationships.jpg).

 

[Galaxiom]

It is certainly a complex data model. I haven't made a detailed analysis but on the first pass it looks to me like you know what you are doing.

I didn't see anything in the relationships that looked out of place.

Some developers prefer to use the same names for the related field in multiple tables but your names look like they would work.

Keep up the good work.

 

[gemma-the-husky]

i think you can get a situation where you have perhaps redundant relations.

eg

customer - related to representative

and then

salesorder - related to customer and ALSO related to representative.

in some cases it might be reasonable to store the representative in the salesorder, and in others not, it may be redundant - Is that the sort of thing you mean?

 

[Lightwave]

Looks decent to me

You've obviously put a bit of effort into naming conventions of ID fields (This becomes increasingly important as complexity of your application increases). I have several databases with more extensive relationship diagrams and they work fine.

If its a complicated subject with several important database tables you quickly get quite a few relationships.

 

[tatarik]

Thanks Galaxiom and Lightwave for the suggestions.
In the future, I'll try to normalize my table field names as much as possible. So that somebody that looks at it for the first time doesn't have an extra layer of complexity to deal with.

Gemma-the-husky also gave me a great hint, I realize now that one of my database's relationships is redundant:

• [tblContractors 1-8 tblContracts AND tblContracts 1-8 tblFWO] is redundant with [tblContractors 1-8 tblFWO] | I Guess we can call this the transitivity of tables' relationships

Regarding this redundancy, I guess I'll delete the [tblContractors 1-8 tblFWO] relationship. Following transitivity concept, I guess it is preferable to have [A>B>C] than [A>C AND B>C]...


I can't spot any other redundancy yet, but you guys answer has been pretty helpful Thanks

T.

 

[tatarik]

Dear all,

After some more thinking, I'm wondering whether the relationships between

• tblTimesheetLines 8-1 tblEmployees 8-1 tblContractors 1-8 tblContracts, and
• tblFWO 8-1 tblContracts

See relationships map.zip for a visual of this. I highlighted the two series of relationships that I think might be redundant.

cheers
T.

 

[Mihail]

I don't know if this is a rule at all, but is a rule for me:
I don't agree to see "loops" in my databases.
Using other words, if I start from one table and follow any relationships chain, this chain must be an open chain.
Regarding to your relationships, as example, you have 5 relationships for table tblContracts.
So, add 4 more table tblContracts (not all at once because is hard to manage), remove 4 relationships and recreate to the tables tblContracts_1, tblContracts_2, tblContracts_3 and tblContracts_4.
I use this approach because that ensure me that if I start from anywhere (in an open chain) I can query for any item in that chain without the risk to have loops in my queries.
Also, if 2 or more chains find the same data (I repeat: Data NOT Table), I'm sure that one relationship is no more necessary.

What I wish to say with "Data NOT Table"
I have 2 metallic parts and a single table tblMaterials. Each one from my metallic parts has a lookup on tblMaterials. But the result is not necessary the same (one is made from steel and the other one from copper).

Hope this can be a kind of help for you.
For me that have worked with no exception.

Cheers.

 

[tatarik]

Thanks for your reply Mihail.

[1]

So, add 4 more table tblContracts (not all at once because is hard to manage), remove 4 relationships and recreate to the tables tblContracts_1, tblContracts_2, tblContracts_3 and tblContracts_4.
I use this approach because that ensure me that if I start from anywhere (in an open chain) I can query for any item in that chain without the risk to have loops in my queries.

Is this really a different database relations setup than simply linking 5 times to the same tblContracts table?
In other words, do you simply do this to make your database easier to understand, or is there a dramatic database structure change?

[2]

Also, if 2 or more chains find the same data (I repeat: Data NOT Table), I'm sure that one relationship is no more necessary.

What I wish to say with "Data NOT Table"
I have 2 metallic parts and a single table tblMaterials. Each one from my metallic parts has a lookup on tblMaterials. But the result is not necessary the same (one is made from steel and the other one from copper).

I am not sure I got your point here.. Are you saving: you have 2 tables, one containing the parts ("2 mettalic parts") and another one the parts' material (tblMaterials)?
Where are the redundant relationships in your example?


Also, I would add that one of the reasons why tblContracts (and that's also the case for other tables) has that many relationships is because I want to avoid data inconsistency.
For example, I don't want to have a FWO without a Contract. Or an Employee without a Contract, and a Contract without a Contractor, .. and so on.
Without these relationships, I would be able to create FWO, Contracts or Employee records that violates these principles (or at least I fear so..).


Thanks for your input

 

[Mihail]

Ups. Sorry.
Add this table to the relationships window, not as table in the database.
Also, in the relationships window play with chaines.

Sorry for not explain that.

Make a copy of your database before that.

 

[Mihail]

In my example is NOT a redundant relationship. It is exactly what I say: The "chain" go to the same table, but not return the same data.
Regarding your relationships it's YOU who decide if tblContracts will return the SAME data for more than one chain.

To add a table to the relationships window, right click in an empty area and choose "Add Table". You will add the same table "tblContracts" multiple times.
To delete a relationship, click it then press delete.
To add a relationship drag the field from table with ONE over the field in table with MANY.
To edit the relationship, right click it then choose EDIT.

 

[michaeljryan78]

Tartarik - I was looking at the diagram and noticed that you have broken one of the Ten Commandments for developers. There are spaces in the field names. This will lead to a bit harder time coding further down the line, having to use brackets and such. At this point in devlopment, you may choose NOT to change the field names becuase that will break queries, reports, forms, etc., but in future endeavours you may want to use CamelCase for the field names. (like UserID, FirstName, LastName)