-3

I have a problem with this:

Database diagram

enter image description here

object_id is a foreign key references 5 tables' "id" columns. So I can not insert for example 5th record to "connected_nodes" table because in "klapan_treh" table 5th record does not exist, but in "ns" table 5th record exists.

My solution is to create for each table separate columns like: ns_id references ns(id), klapan_treh_id references klapan_treh(id) etc.

But do you advise me another improved way?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
LifanSolano
  • 11
  • 6
  • 1
    The request to not refer you to other questions that asked the same thing is not how stackoverflow works. The kind of key you are looking for (check if id exists in one of 6 unrelated tables) does not make sense in a relational database. But you probably want to express some logic with it. To tell you how to correctly implement it in a database model, you need to tell us what you are trying to do express here. Your workaround to add several columns would express that you want to link your 6 tables together (e.g. id 6 of `is_t` with id 9 of `nodef`), which is probably not what you mean either. – Solarflare Jan 12 '18 at 08:48
  • 1
    `mysql` or `sql-server`? Pick the right one! – B001ᛦ Jan 12 '18 at 10:01
  • B001, by the way, my dear friend, mysql and MS SQL Server use the same Transact-SQL language, so i think my links are correct. – LifanSolano Jan 12 '18 at 16:23
  • Solarflare, it is sophisticated. Below Ron Ballard's answer is suitable and he understood my problem immediately without some additional explanations. Thanks for your responds :) – LifanSolano Jan 12 '18 at 16:27
  • 1. No, MySQL & Transact-SQL are 2 different variations on SQL. Only the latter is used by SQL server. 2. RonBallard didn't understand. He guessed. You are not clear. 3. Please [use text, not images/links, for text (including tables)](https://meta.stackoverflow.com/q/285551/3404097). Here that means give relevant DDL in text, and give an ERD with the relevant bits as extra convenience. Eg where is "object_id"? – philipxy Jan 13 '18 at 03:11
  • Possible duplicate of [Is there a way to maintain a db relationship (pk/fk) in the following scenario](https://stackoverflow.com/questions/47708152/is-there-a-way-to-maintain-a-db-relationship-pk-fk-in-the-following-scenario) – philipxy Jan 13 '18 at 03:29
  • You are not clear about what you want and your ERD doesn't make sense since it shows a FK for what you describe as not a FK. So we don't know whether your proposal is a way to do whatever you want. But your proposal is a typical antipattern for SQL/database subtypes. Google my comments re googling about them. And google my comments about always googling many phrasings/versions of your question. This is a faq. – philipxy Jan 13 '18 at 03:33
  • philipxy, Enough. Now it is not relevant, but you still continue to argue with me. Thanks for all. Problem has been solved, that's it. – LifanSolano Jan 15 '18 at 04:05

1 Answers1

0

Your proposed solution is the best one, I think. What you need is to have 5 separate foreign keys in your connected_nodes table. Each one of these points to the id in exactly one of the other tables. I think that is what you are suggesting.

Each foreign key can be optional (nullable) so that if you have 4 connected nodes you have 4 of the foreign keys filled in and the 5th one null.

Ron Ballard
  • 693
  • 6
  • 8
  • This is generally an anti-pattern where subtyping should be used. – philipxy Jan 13 '18 at 03:19
  • The proposed solution will make the foreign keys work, but it is a very unusual structure to have a 5-way connection. If it were my database I would want to consider that very carefully, but since I don't understand what is being modelled from the information we have here, I don't feel able to suggest how it should be done. – Ron Ballard Jan 13 '18 at 09:06