Foriegn Keys Yah or Nah

Question

Non F-K SCHEMA

human
  human_id | name

alien
  alien_id | name | planet

comment 
  comment_id | text 
        1         hello

vote

  to_id | to_type   |  who  | who_type
  1     human     1       alien
  1     comment   1       human

FK- SCHEMA

human
  human_id | name

alien
  alien_id | name | planet

comment 
  comment_id | text 
        1         hello

entity_id 
  entity_id | id  | type
        1     1      human
        2     1     comment
        3     1     alien      

vote

  to_id | who_id
   1       3     
   2       1

I want to ask which one is better ?

First one is without foreign key

Second one is with foreign key

Isnt the second one (with fk key) will be slow as i have to do twice inserting and unnecesary joins in order to get human/ alien name etc.

And what will happen if entity_id reaches a maximum of 18446744073709551615 ?

With voting tables, it's a common practice storing only IDs of the corresponding entities (both voter and votee). So yes, the second approach _in general_ is preferable. BTW, what's `id` in `entity_id` table for? — raina77ow, Jan 15 '17 at 13:34
the `id` in `entity_id` refers to the primary id of each table i.e `human`,`alien`,`comment` etc — , Jan 15 '17 at 13:35
I don't like either option. A column should represent a single domain. In both your examples, you have columns in which id 1 is context-sensitive. — reaanb, Jan 15 '17 at 13:39
Ah, I see now: you basically have a 'Voter/Votee' interface, which can be either Human, Alien, or Comment - a concrete entity. I'd suggest checking [this thread](http://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) - for the similar problem description. — raina77ow, Jan 15 '17 at 13:39
In example 1, `to_id` and `who` both contain a mixture of `human_id`, `alien_id` and `comment_id` and there's no supertype to unify them and prevent duplicate ids. In example 2, the `id` column in the `entity_id` table is again a mix of domains. Indicating the source table in an adjacent field isn't a good approach. If you describe in English what you're trying to achieve, I can suggest an alternative design. — reaanb, Jan 15 '17 at 13:54
what im trying to do is ..... An account can be Either `Human` or `Alien`. Both can write a `Comment` which can be voted up by both human/alien. And Any human/alien can also vote up any other human/alien. @reaanb — , Jan 15 '17 at 14:05

reaanb · Accepted Answer · 2017-01-15T15:02:57.733

I suggest you add a supertype to unify Human and Alien and use this supertype in relationships. I also suggest separating votes on comments from votes on users into separate relationships. Consider the following tables:

This is the basic idea, though somewhat oversimplified. It allows a User to have both Human and Alien details. If required, disjoint subtypes can be enforced with a few additional columns and triggers.

You ask whether a foreign key and joins will be slower. An argument can be made that normalized databases are likely to be more efficient, since redundant associations are eliminated. In practice, performance has much more to do with effective use of indexes than avoiding joins.

If an auto_increment column overflows, the database engine will return an error and refuse to insert more rows. In this case you can adjust the column to use a larger type. When you exceed the space of the largest types in MySQL, it's probably time for a different (or even custom) solution.

Foriegn Keys Yah or Nah

1 Answers1