complex table relationships

[striker73]

I am working on a fairly complex database design. I am drawing out the tables and the relationships between them. Is it a problem to have my tables connected so that the relationships form a complete circle? I have had trouble in the past when creating queries where entities are related in more than way (where you have to adjust the join properties), do you think there is a problem with this? It's kind of hard to explain, but I'll try.

For example, I have a Job table that is related (1:1) with the Client table. The Client table is related (1:m) to the Client_Contacts table. The Client_Contacts table is related (1:1) to the Contact table. The Job table is also related (1:m) to the Job_Contacts table. The Job_Contacts table is related (1:1) to the Contact table. I really hope this makes sense. It might be easier to draw it out.

...and this is only the beginning of the complexity. Let me know your thoughts. Thanks!

 

[BNPMike]

You can have 'circular' relationships. Indeed it is quite common. Remember that no actual link exists - that's a feature of the relational model. The links only start to appear as you create joins in your SQL queries. If you create an SQL query that involves a circular path, the database will simply process the first reference to a table logically ahead of the second reference to it.

Also don't fret about complexity. Practical databases often have hundreds of tables.

 

[montrose]

striker 73, Is this what you're saying:
One contact will always have only one job?
One client will always have only one contact?
One job will have only one job contact?
That's what it looks like to me from the description of your tbls and the proposed relationships. If not, then your design needs to be adjusted. May be helpful to post additional infor about your tbl designs and what you're trying to accomplish. HTH, Montrose Learn what you can and share what you know.