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? 

 

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.

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)

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

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. 

Great. 

 

Glad you found an answer.