-1

I'm trying to make basic database structure with a list of NaturalPersons, LegalEntities and their SocialNetworkAccounts, simplified structure of this tables looks like this:

NaturalPersons table:
 – ID [number] AI PK
 – Name [text]
 – Surname [text]

LegalEntities table:
 – ID [number] AI PK
 – CompanyName [text] unique

SocialNetworkAccounts table:
 – ID [number] AI PK
 – SocialNetworkLink [text]
 – SubjectID [number] FK

The problem is that I can only link SubjectID to either NaturalPersons.ID or LegalEntities.ID and I can only think of these workarounds:

I. I can make two tables for SocialNetworkAccounts, like

SocialNetworkAccounts-NP table:
 – ID [number] AI PK
 – SocialNetworkLink [text]
 – PersonID [number] FK

 SocialNetworkAccounts-LE table:
  – ID [number] AI PK
  – SocialNetworkLink [text]
  – CompanyID [number] FK

But then I dislike having many similar tables in my database as it complicates queries and further development.

II. I can make Foreign Keys in First two tables instead:

NaturalPersons table:
 – ID [number] AI PK
 – Name [text]
 – Surname [text]
 – SocialNetworkLinkID [number] FK

LegalEntities table:
 – ID [number] AI PK
 – CompanyName [text] unique
 – SocialNetworkLinkID [number] FK

SocialNetworkAccounts table:
 – ID [number] AI PK
 – SocialNetworkLink [text]

But then I won't be able to add multiple entries (social links) per person / company.

III. I was thinking about another option, where I fill either PersonID or CompanyID:

NaturalPersons table:
 – ID [number] AI PK
 – Name [text]
 – Surname [text]

LegalEntities table:
 – ID [number] AI PK
 – CompanyName [text] unique

SocialNetworkAccounts table:
 – ID [number] AI PK
 – SocialNetworkLink [text]
 – PersonID [number] FK
 – CompanyID [number] FK

But SQL requires data in FK columns to be present. And I need something like a "composite foreign key", where it is "either PersonID or CompanyID" is present.

So I'm puzzled about best practices for such situations. I'm also not sure how the feature (if exists) might be called.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Mike
  • 1,979
  • 2
  • 16
  • 29
  • What is your 1 specific researched non-duplicate question re how/why you are 1st stuck on what step among which steps following what published presentation of what design method/process given what? [ask] [Help] [Why is asking a question on "best practice" a bad thing?](https://meta.stackexchange.com/q/142353/266284) [Strategy for “Which is better” questions](https://meta.stackexchange.com/q/204461) Putting words in scare quotes does not clarify the idiosyncratic meaning that you don't make clear by actually saying what you mean. – philipxy Nov 29 '22 at 21:55
  • Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) – philipxy Nov 29 '22 at 21:59

1 Answers1

1

In such situation I would create two tables

  1. NaturalPersonsSocialNetworkAccounts with not null foreign keys to NaturalPersons.ID and SocialNetworkAccounts.ID
  2. LegalEntitiesSocialNetworkAccounts with not null foreign keys to LegalEntities.ID and SocialNetworkAccounts.ID

I would call these "association tables".

GWR
  • 204
  • 1
  • 5