Jump to content

Base Table vs TO - foreign key


centauri272
 Share

Recommended Posts

Hi there

I'm a newbie but eager to learn!! Sorry if the question is absurd.

1. I create 2 new Tables (aka Base Tables) named Company and Contact. Both have a primary key.

2. In the relationship graph, I have the TOs and want to build a relationship between them (Anchor-Buoy). So I will need foreign keys in both.

My question: Do I already create the foreign key fields in the Base Tables or only when building the relationship (TO)?

Sorry again...😉

 

Link to comment
Share on other sites

I always have a foreign key field in tables I am creating once I wire it up to a parent I will rename the foreign key field to identify it's parent such as uid_company you don't necessarily need a foreign key in company unless it is a child of some other entity.

 

  • Thanks 1
Link to comment
Share on other sites

8 minutes ago, Ocean West said:

I always have a foreign key field in tables I am creating once I wire it up to a parent I will rename the foreign key field to identify it's parent such as uid_company you don't necessarily need a foreign key in company unless it is a child of some other entity.

 

So if I take the database I'm working on (music sheet db), I have 3 Base tables: WOR_Work, PER_Person and GEN_Genre.

In the graph, I rename the PTO T01_Work, T02_Person, T03_Genre and create buoys.

Do you mean: in the base tables I already create foreign key fields, ex _IDf_Work, _IDf_Person, etc. , even though only the TO will be in a relationship?

Link to comment
Share on other sites

2 hours ago, centauri272 said:

in the base tables I already create foreign key fields, ex _IDf_Work, _IDf_Person, etc. , even though only the TO will be in a relationship?

Fields are property of a (base) table, not of any particular table occurrence. IOW, all TOs of the same table have exactly the same fields.

3 hours ago, centauri272 said:

So I will need foreign keys in both.

As already noted, in a one-to-many relationship, only the child (many) table needs to have a foreign key to the parent (one) table. This key is matched to the parent's primary key.

 

  • Thanks 1
Link to comment
Share on other sites

This is just an opinion, but keep it simple. 
 

Company->Company_Contact. Related by Company::Id ->Contact::CompanyId. I know some name table occurrences with codes, but I’ve not seen the need or the payoff.  I line up all my anchor table occurrences vertically and alphabetically. I almost never scroll right in the relationship graph. It’s kept me efficient this way. 
I create foreign keys as I need them, but ideally you’ve planned most of the data model before you open FileMaker.

in fact, we’d rather help you there before you go off on the wrong direction. 

  • Thanks 1
Link to comment
Share on other sites

15 hours ago, bcooney said:

This is just an opinion, but keep it simple. 
 

Company->Company_Contact. Related by Company::Id ->Contact::CompanyId. I know some name table occurrences with codes, but I’ve not seen the need or the payoff.  I line up all my anchor table occurrences vertically and alphabetically. I almost never scroll right in the relationship graph. It’s kept me efficient this way. 
I create foreign keys as I need them, but ideally you’ve planned most of the data model before you open FileMaker.

in fact, we’d rather help you there before you go off on the wrong direction. 

I agree. Actually I had planned the data model, but everything crashed in my head when I read on a website that the Relationship Graph IS NOT an ERD, and when I decided to go from spider to anchor-buoy. I can assure you that I've been going in the wrong direction for quite some time...😁

This is what happens when you do first and think after...

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.