Can I start with one-to-many relationships and easily add a join table later on for many-to-many?
This is with FM Pro 10 Advanced.
I'm creating a database of customers. Each customer has contacts,. So I have a Customers table and a Contacts table. The Customers are some organization, usually a company or a school or something.
Each Customer can have multiple Contacts. Contacts are people.
There are also other tables, but just for example, I wanted to ask a question about just these two tables. I don't want to do something that's irreversible, or a nuisance to adjust later. But I also don't want to go overboard at the beginning with stuff I don't need.
I think, I'm pretty sure, that all the people in the Contacts table work for just one Customer. So my first thought is in the relationship graph to join the Customer ID primary key from the Customer table to the Customer ID foreign key in the Contacts table. Then I can have a one-to-many relationship with multiple Contacts for each Customer.
(By the by, is there a naming convention people tend to use to distinguish their primary and foreign keys, or is using the same field name in both tables considered good practice?)
But it occurs to me that it is possible for a Contact to maybe work for two Customers. For example an independent contractor who helps with two different Customers. In that case it's a many-to-many relationship and I need a join table between them, right? I'm a little hazy about doing that. I imagine I'll get used to it once I try it, but my question is if I start out without a join table and assume that each Contact just works for one Customer and then later on find out that's not true is it a mess to change? Would it be better to start with a join table from the outset, even if I don't have any of Contacts shared between Customers right now? Or is it easy to create a join table later on if that happens?
As a side question, what would be a good naming convention for such a join table between Customers and Contacts?