A relational question from a quite simple implementation.

[fmow]

The main table of my database is Projects, this is related as a child record to Companies which in turn has Representatives_of_Companies as a child table. Within the Project table there are fields that are to be populated via the Companies table, namely Client and Associate_Company (ten fields from 1 to 10).

 

At the moment the Project table has an fk_client_serial for the Client field but the Associate_Company (1-10) only use a list derived from Companies with explicit relation and pk/fk interchange. 

 

 

So, my query, is simple. Is this good practise the way I am going about this or is it just an inept way to go about it. 

 

 

Should I try to create a new TO for Companies and try to relate Associate_Companies?

Should I just fill Associate Companies with a serial no. (pk) of Companies from the parent table? 

 

[iammontoya]

I didnt really follow your example very well, but the rule of thumb is that all your tables should simply have a one-to-many relationship. Based on what I gathered from your example, I would say you need an extra table to avoid having a many-to-many relationships in your database design.

[IT_User]

I agree with iammontoya.  Relationships should be one to many.

Like you were asking, Replace your Associate_Companies (1-10) fields with a Join table back to a new TO of the Companies table.

So in other words:

Projects - Join_Table - Companies (call Associate_Companies)

[Lee Smith]

Have you done an ERD (Entity Relationships Diagram) of your files? Relationships can be hard to understand, but there are some resources available help you. I know that there are some videos on YouTube that can help you, and you might look into OmniGraffle Professional which come with a video, or become a member of Lynda.com which has several different courses and levels of courses including ERD and Relationship Graph. There are other training available, including skeletonkey.com/, database pros, both are sponsors of our Forum, along with ISO FileMaker Magazine provides, etc.

 

HTH

Lee

[fmow]

Have you done an ERD (Entity Relationships Diagram) of your files? Relationships can be hard to understand, but there are some resources available help you. I know that there are some videos on YouTube that can help you, and you might look into OmniGraffle Professional which come with a video, or become a member of Lynda.com which has several different courses and levels of courses including ERD and Relationship Graph. There are other training available, including skeletonkey.com/, database pros, both are sponsors of our Forum, along with ISO FileMaker Magazine provides, etc.

 

HTH

Lee

Hi Lee! 

 

I think I 've sorted this out, so far at least, via a joint table.

 

Many thanks for the heads up for the training videos and general info. I am a member of Lynda.com, as well as vtc. I wasn't aware of your two sponsors but I am definitely going to check them out. 

[Lee Smith]

Great. 

 

Glad you found an answer.