mac | pc How to correct multiple identical primary key fields

[stefansaeys]

I'm facing an issue caused by wrongfully exporting the initial database (which is not available anymore).

3 tables: properties, organisations (companies) and contacts.

 

Properties::REF_tenant = Companies::REF. 

Companies::__pkCompaniesID = Companies_contacts::__fkCompaniesID (join table between Companies and Contacts)

companies_contacts::__fkContactsID = Contacts::__pkContactsID

 

The goal is that 1 property can be rented by multiple companies and/or can have multiple contacts.

For now the multiple companies are the same companies, but all with another contact.

 

Therefore Companies::REF should be a primary key (and unique). Unfortunately it isn't. If multiple companies (basically contacts) are linked to a property it is being done by using the same REF; which are separate records in the table companies, but all of them have a different contact.

 

For example (screenshot attached):

Property::REF 22654 links to Companies:REF 2203.

REF 2203 has four records in table Companies; all with identical company name, but different contact.

I think I should merge these records but I don't know how.

 

Hopefully my question makes some sense; if created a dummy table for clarification if needed. This is available at https://www.dropbox.com/s/bosqbztj7zmf9k5/dummy.fmp12?dl=0

 

Any suggestions or help greatly appreciated.

 

Stefan 

[comment]

I lost you at this point:

 

The goal is that 1 property can be rented by multiple companies and/or can have multiple contacts.

 

Are you sure about this? If a property can have multiple tenants, then your Properties table should not have a REF_tenant field, and the problem begins here - not with multiple companies having the same REF value.

[Wim Decorte]

The table you are missing is "Tenants", which is the link between Properties and Companies.

 

Your Companies table is not in the right shape either; I did a quick search and found 9,200 duplicates.  Each company in that table should be unique.  If one contact person can act for more than one company then you also need a join table between people and companies to avoid cluttering either table.

[stefansaeys]

I lost you at this point:

 

 

Are you sure about this? If a property can have multiple tenants, then your Properties table should not have a REF_tenant field, and the problem begins here - not with multiple companies having the same REF value.

 

Yes, they should be able to point to multiple tenants/companies(contacts). But for now al the REF values are pointing to one (the same) company that only has other contact(s). I understand that the value REF value needs to be unique, but I can't find a solution to merge them into one company that has several contacts.

 

 

The table you are missing is "Tenants", which is the link between Properties and Companies.

 

Your Companies table is not in the right shape either; I did a quick search and found 9,200 duplicates.  Each company in that table should be unique.  If one contact person can act for more than one company then you also need a join table between people and companies to avoid cluttering either table.

 

Do you mean I have to create a join table "Tenants"?

The duplicates all refer to the same company, only the contacts are different.

[comment]

I think at this point we are trying to figure out what you should have - before figuring out how to translate what you do have to that. When you say:

 

Yes, they should be able to point to multiple tenants/companies(contacts).

 

it doesn't mean much. What is your solution actually about? Are those your properties, that you lease out to various tenants (companies)? If yes, than you should have a join table of Leases between Properties and Companies (this is assuming you want to keep a history of past leases for each property). And, assuming a company has multiple contacts, and that there is a one dedicated contact assigned to handle each lease, your entire structure should look something like this:

 

 

 

 

 

 

[Wim Decorte]

 

Do you mean I have to create a join table "Tenants"?

 

 

Whether a table is a join table or any other table does not matter.  You should have a table for all the known entities in your scope.  Look for nouns when you are trying to decide on tables.  A tenant is clearly an entity so it should have its own table.

 

A tentant describes a couple of things:

- what property it leases

- what owner it leases from

- start date

- end date

- agreed amount

[comment]

A tentant describes a couple of things:

- what property it leases

- what owner it leases from

- start date

- end date

- agreed amount

 

"A tenant is a person that occupies a property rented from a landlord." A table of Tenants would NOT describe any of these. I believe you meant a table of Leases? But even then I would take out:

 

- what owner it leases from

 

because that's an attribute of the property, not of the lease.

[stefansaeys]

lease.gif

 

 

I haven't thought about this approach yet.

 

A property can be "owned" or "rented" by (in the current database) 1 company. So this would be an owner or tenant. That company can have 1 or more contacts, (in the current database) from the same company. To link those companies to its properties (can be multiple) the same REF number is being used.

 

Keeping a history isn't needed (at this time).

[comment]

I am afraid that's still rather vague. If a property can be linked to one company only, and a company can have many contacts, then you should have a very simple arrangement of:

 

Properties >- Companies -< Contacts

 

I don't see why you need a join table between Companies and Contacts, unless one person can serve as a contact for more than one company.  The other point that's still not clear is the relationship of Contacts to Properties - if any. Is one of the company's contacts assigned to a specific property the company may own or lease? Or are all of company's contacts equal with respect to all of company's properties?

[stefansaeys]

I am afraid that's still rather vague. If a property can be linked to one company only, and a company can have many contacts, then you should have a very simple arrangement of:

 

Properties >- Companies -< Contacts

 

I don't see why you need a join table between Companies and Contacts, unless one person can serve as a contact for more than one company.  The other point that's still not clear is the relationship of Contacts to Properties - if any. Is one of the company's contacts assigned to a specific property the company may own or lease? Or are all of company's contacts equal with respect to all of company's properties?

 

I need the join table between companies/contacts because a contact can be linked to multiple companies (independent consultants,…)

I’m not sure if there is a real relation between Properties and contacts…..I think there is, because a contact can be assigned to one specific or multiple companies and/or a company can be assigned to one or multiple properties.

 

Does this mean there is no relation between properties and contacts?

 

Not all company contacts are always assigned to one property.