I am trying to create a contact information system, but I am very stuck with the database modelling.
What I am thinking about today is having a person table with the rudimentary information about a person (name, forename, surname, ssn, sex, date-of-birth etc.). Them I am thinking about creating new tables for things like addresses, email addresses, companies, phone numbers, nicknames, aliases etc. etc.
Creating these with FKs to the main person entry isn`t a problem. But, example for email addresses. A user will have several email addresses for several use cases. One which is private, and one which will be somewhat connected to the company.
I am wonder if there is any sane way to connect the company email address with the company entry in the companies table?
I guess I could have an FK from the email address to the company table, but what then if the link shouldnt be to the company, but rather to some other entity? Like an organisation, or an alias, or an email address thats only used in certain situations?
The same situation would obv. also apply for things like phone number, postal/visit address etc. etc.
You cannot model things like "a person has an email address which is linked to just about anything". You need to be specific about the relationships.
If you want to be able to add specific relationships without having to modify existing tables (you should), then you better not add a FK from email address to company. After all, the entity "email address" doesn't know that it has a relationship to a company, because some email addresses may have other relationships. It is not in the nature of an email address to belong to a company, but an additional aspect you want to add.
That said, you can add relationship tables, which express thes kinds of relationships. For every new relationship you either need to add another table (not too bad IMO), or use a single table and add columns on the non-email-address side, pointing to company, organization or whatever.