database - 1 to 1 relation with more than 2 tables -

example. in system have 1 object used in 1 1 relation several other types of objects. let's call object used address, , 2 objects use company , person. every company , person has 1 address, each address unique in system, each address referenced either person or company.

i have 2 questions.

question 1) relation called? examples of 1 1 relation i've found on web between 2 tables only, while 3 way 1 1 relation.

question 2) table structure best captures relation? see @ least 2 potential designs both flawed.

design a) uses 2 nullable foreign keys constraint @ least 1 not null. compound foreign key.

- company - pkcompanyid ...  - person - pkpersonid ...  - address - pkaddressid fkpersonid <nullable> fkcompanyid <nullable> ... 

design b) each company , person identifies address uses.

- company - pkcompanyid fkaddressid ...  - person - pkpersonid fkaddressid ...  - address - pkaddressid ... 

design a) seems more conventional use of foreign keys me in object being used identifies objects reference it, haven't seen examples of being done multiple foreign keys this.

design b) looks reverse of how 1 1 relation modelled.

it occurs me in conventional 1 1 relation (below) fact fkpersonid non-nullable unique foreign key helps enforce 1 1 relation in actual table structure (it guarantees each address referenced single person references no other address). neither of designs achieves this. in design a) has ensure 1 , 1 of fkcompanyid or fkpersonid null ensure consistency. in design b) has ensure no fkaddressid in company or person duplicated in either table. causes me suspect i'm making mistake here.

- person - pkpersonid ...  - address - pkaddressid fkpersonid ... 

without effort can see other designs, e.g. using guid pkcompanyid , pkpersonid , single foreign key in address again don't see examples of strategy in of online resources i've researched.

i've exceeded limited knowledge of database design , use guidence please. best way handle this?

update response walter mitty's answer

i think single table inheritance not appropriate situation. although i've used examples company , person here, in real application these both large complex tables have nothing in common other fact both own address. not data different, represent different concepts.

my actual entities each have instance of same type organisation, project , projectelement. organisation represents companies licenced use system. project represents projects created users of particular organisation, projectelement represents piece of project data.

the thing have instance of settings object. default settings provided @ organisation level. when project created gets copy of organisation's settings. components within project copy of project's settings.

this architecture supports several use cases, including when settings organisation modified changes impact new projects, not projects have been created. projectelements inherit settings of project may optionally override specific project settings.

further complications include fact organisations, projects , projectelement don't have instance of 1 shared type, several. there several different types of settings objects each own table. makes awkward if not infeasible use id's of instances of shared types primary keys of referencing objects. i'm using entity framework core orm doesn't support views.

your answer useful , gives me think about, far seems me design 2 best achieves requirements. there inherently unsound design 2 makes unsuitable?

your question looks case of type , subtype (or class , subclass). before try dealing 2 questions, i'm going try reframe it, however, 2 relationships.

first, i'm going invent new entity, i'll call "contact". each address in system referenced contact, , each contact has 1 or more addresses.

each contact either person or company, not both.

now, conceptually @ least, first connect address contact, connect contact person or company. @ logical , physical levels, may overkill. is, may add complexity without adding value. cannot emphasize enough.

but conceptual clarity valuable own sake.

now on question 1.

the relationship between contact , address classic "has-a" style relationship, many have seen before.

but relationship between contact , either person or company, different kind of relationship, 1 may not have seen before. it's called various names, , answer question 1.

it called precisely "is-a relationship". in er modeling, goes name "specialization/generalization" relationship. in object modeling, it's called "class/subclass relationship" although buzzwords "derived class" or "extended class" used. it's connected object concept of inheritance.

a word of caution object modeling, however. if going object modeling , data modeling in same project, had better have pretty deep understanding of both of them, , how each of them adds value project. otherwise, fall trap of thinking 1 model or other incomprehensible or useless. smarter people me have fallen trap many times.

now second question. there 2 ways can design tables situation. 1 called "single-table-inheritence" , other called "class-table-inheritance". reason put hyphens in there tags in area these 2 topics, , looking on questions , info under these 2 tags might you.

single table inheritance.

we have 2 tables, 1 address , 1 contact.

address:

addressid (pk) contactid  (fk references contact) street address city, state, zipcode, etc. 

contact:

contactid (pk) contacttype  (person or company, coded) first name last name, company name, etc. 

company name null in case of persons, first name, last name null in case of companies).

class table inheritance

we have 4 tables, address, contact, person, , company

address:

addressid (pk) contactid  (fk references contact) street address city, state, zipcode, etc. 

contact:

contactid  (pk) 

person:

contactid  (pk, , fk references contact) first namme, last name, etc. 

company:

contactid (pk , fk references contact) company name etc. 

for case, recommend single table inheritance. complicated cases of is-a relationships class table inheritance can work out better.