0

I have created a test scenario like this:

I have three tables namely father, child, food.

Father table has primary auto increment key and a name column. Child table has primary auto increment key and a name column. Food has a column in which I want a foreign key and a column for food name.

father
-----------
id    name
1     kevin
2     adam

child
-----------
id    father_id    name
1     1            fred
2     1            john
3     2            alan

food
------------
person_id                name
1(from father table)     pizza
2(from child table)      burger
1(from child table)      hotdog

Now the food table has anomaly. I can't distinguish that (1 pizza) is for father or child...

Test Cases: select food.name from food where father.id = 1; OUTPUT: pizza select food.name from food where child.id = 1; OUTPUT: hotdog

piotrgajow
  • 2,880
  • 1
  • 22
  • 23
Ghazni Ali
  • 256
  • 2
  • 13
  • Possible duplicate of [Foreign key referring to primary keys across multiple tables?](https://stackoverflow.com/questions/668921/foreign-key-referring-to-primary-keys-across-multiple-tables) – philipxy Jun 05 '17 at 01:53
  • Google my comments about "database subtyping". (You'll see it's associated with "foreign key to two/multiple tables.) (Although there isn't actually a FK here.) In fact if you had just *googled your title or other clear statement of your problem* you would find this is a faq with many duplicates. Eg [this answer](https://stackoverflow.com/a/26093733/3404097). – philipxy Jun 05 '17 at 01:56

1 Answers1

2

I believe that design is pretty bad. Why don't you create a "person" table, populate it and determine if a person is a child or father in it, then use person id in food table?

Utku Tombul
  • 176
  • 1
  • 1
  • 7
  • It is important to make two tables for Father and Child but we can edit the food table is there any other thing you can suggest ? – Ghazni Ali May 27 '17 at 12:50
  • You could add person_type field to the food table, and assume if person is father or child by that. Of course you would need to remove foreign keys or any kind of foreign key control. Again, that'd be a bad design in my opinion. – Utku Tombul May 27 '17 at 12:52
  • @GhazniAli If something is important to the question, put it in the question. (But please use enough words to be very clear.) – philipxy Jun 05 '17 at 01:49